CODEWITHSUNDEEP
c#sql-serverstored-procedures
mshwf
mshwf

Reputation: 7449

How to tell if a record is already exists in the database before executing stored procedure?

I want to create a stored procedure to insert data into a table in the database, but it should be unique so I check first for the incoming parameter:

create procedure SP_Insert
    @name varchar(50)
AS
    if not exists (select Name from Employees where Name = @name)
    begin 
        insert into Employess (Name) 
        values (@name)
    end

My question is, how to tell in my code if the passing parameter hasn't been accepted as a unique value after the execution of the stored procedure?

In my form I have a button (Insert) and a textbox (name), when the user click insert the text value is passed to the stored procedure, and I want to spring a message box warning the user of duplicated entry

Upvotes: 0

Views: 2000

Answers (4)

Scott Hannen
Scott Hannen

Reputation: 29207

You can do this:

insert into Employess (Name) 
select @name
where not exists (select * from Employees where Name = @name)

select @@rowcount

Now the @@rowcount (returned to the caller) is either zero or one depending on whether there was an insert.

var recordsUpdated = command.ExecuteScalar();

Actually you could skip select @@rowocount and not explicitly return anything.

var recordsUpdated = command.ExecuteNonQuery();

That returns the number of affected records. I prefer to be more explicit. Someone could come behind and alter the procedure so that it does something else that changes @@rowcount. (Why? But they could.) And they might not know that something downstream is depending on that affected record count. But if it's explicit, whether a selected value or an output parameter, then someone can tell that something else depends on that value.

Upvotes: 1

Sparrow
Sparrow

Reputation: 2583

Stored procedure can return a value. You can change your SP into something like this:

create procedure SP_Insert
@name varchar(50)
AS
BEGIN
    if not exists (select Name from Employees where Name=@name)
    begin 
       insert into Employees (Name) Values (@name)
       Return 0
    end
    else begin
       Return 1
    end
END

Here is the link to MSDN article with more details and examples: [https://msdn.microsoft.com/en-us/library/ms188655.aspx]

Upvotes: 0

Whencesoever
Whencesoever

Reputation: 2296

create procedure SP_Insert
@name varchar(50), @result bit output
AS
if not exists (select Name from Employees where Name=@name)
begin 
insert into Employess (Name) Values (@name)
set @result = 1
End
else set @result = 0

Upvotes: 0

matt-dot-net
matt-dot-net

Reputation: 4244

Use @@ROWCOUNT to determine that a row was affected and return the value as a parameter. See this answer: How can I get the number of records affected by a stored procedure?

Upvotes: 1

Related Questions