views:

141

answers:

6

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, 
                         @cunt int output

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

A: 

select *

from yourRandomTable

where yourRandomCondition

if yourRandomCondition.Count > 0 return

else insert

Woot4Moo
A: 
if Exists select * from Yourtable WHere Your Criteria
begin
  update ...
end
else
begin
  insert ...
end
No Refunds No Returns
Will the EXISTS lock the PK being queried such that there is no possbility of another user doing an INSERT in the meantime? If not then there is the posibility that the EXISTS finds NO row, but the subsequent INSERT fails because the row has already been inserted (by then). I don't know the answer WRT the Isolation level, hence my question.
Kristen
Exists will also not work for update. Suppose if user updates a record and enters a value that already exists in database then exists will allow user perform update operation that is not correct
Abhishek Kapuria
A: 

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
AdaTheDev
Thank for your reply. But being a beginner still i am not able make your reply work for me.
Abhishek Kapuria
You need to be more specific, what part is not working?
Aaron Bertrand
Let me explain you the exact scenario. I am working in ASP.net I have tw0 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,@cunt int output Now your query on updating/inserting @field1,@field2 on basis of @id
Abhishek Kapuria
I think you need to add more detail to your original question. Impossible to decipher from an unformatted comment.
Aaron Bertrand
+1  A: 

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.

OMG Ponies
A: 

Exists is better than Count

RocketSurgeon
Exists wont work for update. Coz it will allow user to enter data in table that is already present
Abhishek Kapuria
+1  A: 

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)
Damir Sudarevic
+1 for the MERGE syntax in Sql2008.
Pure.Krome