execute stored procedure based on condition

Question

i tried to insert data in database using stored procedure but according to some condition. for example if data is already exists in database the the stored procedure should not be executed. i am doing the following code but it return 0 every time rather then i want. What can i do

ALTER PROCEDURE dbo.SaveUser
(
  @cid int,
  @firstname varchar(20),
  @lastname varchar(20),
  @dob datetime,
  @gender varchar(20),
  @add varchar(100),
  @email varchar(40),
  @quali varchar(20), 
  @yop varchar(15), 
  @exp varchar(10),
  @pass varchar(20)

)
AS

declare @result int

if  EXISTS( select Email_ID from Registration where Email_ID=@email )
begin
    set @result=4;
    return @result;
end

else
begin
    insert into Registration values(@cid, @firstname, @lastname, @dob, @gender, @add, @email, @quali, @yop, @exp, @pass );
    set @result =0;
    return @result;
 end

When you say @result is always 0, does the data get inserted everytime you call the SP? — rcs, Sep 25 '13 at 06:00
"return 0 every time" - I expect you're examining the wrong output - how are you calling the stored procedure and/or obtaining the 0? — Damien_The_Unbeliever, Sep 25 '13 at 06:01
Please show the code that's calling this stored procedure/obtaining the 0. The code you've shown looks correct, so the problem is in the code you're not showing us. That's exceptionally difficult to debug. — Damien_The_Unbeliever, Sep 25 '13 at 08:21

Nadeem_MK · Answer 1 · 2013-09-25T06:11:11.133

1

You can simply proceed as follows;

ALTER PROCEDURE dbo.SaveUser
(
  @cid int,
  @firstname varchar(20),
  @lastname varchar(20),
  @dob datetime,
  @gender varchar(20),
  @add varchar(100),
  @email varchar(40),
  @quali varchar(20), 
  @yop varchar(15), 
  @exp varchar(10),
  @pass varchar(20)    
)
AS
BEGIN

DECLARE @return int

if NOT EXISTS(select Email_ID from Registration where Email_ID=@email) 

insert into Registration values(@cid, @firstname, @lastname, 
                                @dob, @gender, @add, @email, 
                                @quali, @yop, @exp, @pass )
If (@@ERROR <> 0)
Begin
  Set @return = 4
Else
  Set @return = 0
End

Return @return

END

edited Sep 25 '13 at 06:11

answered Sep 25 '13 at 05:58

Nadeem_MK

7,533
7
50
61

but i want to provides some error message to user eg. user already exist, registration successful etc. for that i want some return value – user2353848 Sep 25 '13 at 06:02
Use the `@@ERROR` parameter, `IF (@@ERROR <> 0) set @result =0` – Nadeem_MK Sep 25 '13 at 06:04
will u explain it i am new for sql server – user2353848 Sep 25 '13 at 06:06
it still return 0 what i can do – user2353848 Sep 25 '13 at 06:25
If it returns zero, means the data has been inserted. – Nadeem_MK Sep 25 '13 at 06:32
Check if you have a transaction where the stored procedure is being called. Must debug to know further :( – Nadeem_MK Sep 25 '13 at 06:59

score 1 · Answer 2 · answered Sep 25 '13 at 06:14

You can use raiseerror event for this scenario

if  EXISTS( select Email_ID from Registration where Email_ID=@email )
begin
    RAISERROR(N'Email id already exist.', 10, 2,'')
end

Here 10 is severity level and according to severity level sql server identified as info meassage or exception. For more information

RaiseError

Database Engine Severity Levels

score 0 · Answer 3 · answered Sep 25 '13 at 06:11

0

Try this one -

ALTER PROCEDURE dbo.SaveUser 
(
    @cid INT,
    @firstname VARCHAR(20),
    @lastname VARCHAR(20),
    @dob DATETIME,
    @gender VARCHAR(20),
    @add VARCHAR(100),
    @email VARCHAR(40),
    @quali VARCHAR(20),
    @yop VARCHAR(15),
    @exp VARCHAR(10),
    @pass VARCHAR(20)
)
AS BEGIN

    IF NOT EXISTS(
        SELECT 1 
        FROM dbo.Registration 
        WHERE Email_ID = @email
    ) BEGIN

        INSERT INTO dbo.Registration
        SELECT @cid, @firstname, @lastname, @dob, @gender, @add, @email, @quali, @yop, @exp, @pass

        RETURN 0;

    END

    RETURN 4;

END

answered Sep 25 '13 at 06:11

Devart

119,203
23
166
186

then how can i get this value in c# – user2353848 Sep 25 '13 at 06:43
Check this topic: http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value – Devart Sep 25 '13 at 06:46

execute stored procedure based on condition

3 Answers3