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

Question

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

so what if you had 20 people with the name `Mohamed` sounds like you need to have a different column(s) to check if a record exist also do some basic reading on `SQL` this will help you to better solution your issue. — MethodMan, Aug 16 '16 at 19:27
that is what comes to my mind when I wrote the example above, my real table is Countries table. — mshwf, Aug 16 '16 at 19:29
then you should be showing your code / example based on the actual table.. come on now.. — MethodMan, Aug 16 '16 at 19:32
You should also avoid the sp_ prefix, or even better just skip the prefix entirely. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Aug 16 '16 at 19:35

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

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?

edited May 23 '17 at 11:53

Community

1
1

answered Aug 16 '16 at 19:16

matt-dot-net

4,204
21
24

1

Best approach here. – S3S Aug 16 '16 at 19:25

Scott Hannen · Answer 2 · 2016-08-16T20:31:20.503

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.

score 0 · Answer 3 · answered Aug 16 '16 at 19:19

0

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

answered Aug 16 '16 at 19:19

Whencesoever

2,218
15
26

Sparrow · Answer 4 · 2016-08-16T19:41:36.177

0

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]

edited Aug 16 '16 at 19:41

answered Aug 16 '16 at 19:34

Sparrow

2,548
1
24
28

1

This won't work. The return statement returns an integer which is intended to indicate a status of the execution. If you want to get a value returned you should use an OUTPUT parameter. – Sean Lange Aug 16 '16 at 19:36
Yes, a stored procedure can return a value - but **only** an `INT` value - not a string!! – marc_s Aug 16 '16 at 19:37
@SeanLange I think this answer is correct. See http://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value Please remove down-vote. – Diego Aug 16 '16 at 19:43
@Diego originally this was returning a string literal. I still would downvote this because it is using the return statement to pass data back. Data being returned should be using an OUTPUT parameter. It is even indicated as such in the article that is now linked to in the answer. But since it will at least perform the requirements I suppose I wouldn't downvote it. – Sean Lange Aug 16 '16 at 20:31
@Diego I am guessing that you upvoted this to "offset" the downvote. That is not how this is supposed to work. If this was using output parameters it would be upvote worthy in my opinion. – Sean Lange Aug 16 '16 at 20:34

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

4 Answers4