CODEWITHSUNDEEP
sqlsql-server
user2353848
user2353848

Reputation: 157

execute stored procedure based on condition

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

Upvotes: 0

Views: 7374

Answers (3)

Chamal
Chamal

Reputation: 1449

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

Upvotes: 1

Devart
Devart

Reputation: 121902

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

Upvotes: 0

Nadeem_MK
Nadeem_MK

Reputation: 7689

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

Upvotes: 1

Related Questions