CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Abhishek Kapuria
Abhishek Kapuria

Reputation: 41

Update and Insert Stored Procedure

I want to create a stored procedure that performs insert or update operation on a column if that column does not contains a value that already exists in database it should allow insert when COUNT(field) = 0 or update when COUNT(field)=0 or 1 And I should know that either of these operation is performed or not. Please solve my problem using COUNT not Exists because that won't work for UPDATE.

I am working in ASP.net - I have two columns of a table that are needed to be kept unique without using the unique constraint. So I want a procedure like this:

create proc usp_checkall @field1 varchar(20), 
                         @field2 varchar(20), 
                         @ID int, 
                         @count int output

Now your query on updating/inserting @field1 & @field2 on basis of @id

Upvotes: 4

Views: 5538

Answers (4)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

If you happen to have SQL Server 2008, you could also try:

MERGE dbo.SomeTable AS target
    USING (SELECT @ID, @Field_1, @Field_2) AS source (ID, Field_1, Field_2)
        ON (target.ID = source.ID)
    WHEN MATCHED THEN 
        UPDATE SET Field_1 = source.Field_1, Field_2 = source.Field_2
    WHEN NOT MATCHED THEN   
        INSERT (ID, Field_1, Field_2)
        VALUES (source.ID, source.Field_1, source.Field_2)

Upvotes: 8

OMG Ponies
OMG Ponies

Reputation: 332661

Use:

INSERT INTO your_table
  (column)
VALUES
  ([ your_value ])
WHERE NOT EXISTS (SELECT NULL
                   FROM your_table 
                  WHERE t.column = [ your_value ])

That will work on SQL Server, MySQL, Oracle, Postgres. All that's needed is to use the db appropriate variable reference. IE: For MySQL & SQL Server:

INSERT INTO your_table
  (column)
VALUES
  ( @your_value )
WHERE NOT EXISTS (SELECT NULL
                   FROM your_table 
                  WHERE t.column = @your_value)

To see if anything was inserted, get the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147304

This kind of approach will do the trick. @AlreadyExisted could be an OUTPUT parameter on the sproc for your calling code to check once it's returned.

DECLARE @AlreadyExisted BIT
SET @AlreadyExisted = 0

IF EXISTS(SELECT * FROM YourTable WHERE YourField = @FieldValue)
    BEGIN
        -- Record already exists
        SET @AlreadyExisted = 1

        UPDATE YourTable
        SET....
        WHERE YourField = @FieldValue
    END
ELSE
    BEGIN
        -- Record does not already exist
        INSERT YourTable (YourField,....) VALUES (@FieldValue,.....)
    END

Upvotes: 0

No Refunds No Returns
No Refunds No Returns

Reputation: 8346

if Exists select * from Yourtable WHere Your Criteria
begin
  update ...
end
else
begin
  insert ...
end

Upvotes: 0

Related Questions