Update and Insert Stored Procedure

Question

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

Answer 1

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)

Answer 2

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.