Pow4Pow5 Pow4Pow5 - 6 months ago 13
SQL Question

SQL Stored Procedure IF ELSE logic error

I am trying to implement an insert stored procedure. Basically, how it works is the

stored procedure
will check whether the record exists or not, then proceed to perform insert. A variable
@status
will be used as indicator. However, I found out a particular problem with this
query
is that when I execute the
query
, it will return the result
@status = 1
no matter the data existed or not. However, the
INSERT
function is fine without any problems just the
@status
. The following is my implementation:

CREATE PROCEDURE save_proc
(
@userid varchar(10)
@name varchar(30)
)

AS

DECLARE @status int

if exists ( SELECT * FROM table1 where userID = @userid AND userName = @name)
SET @status = 0

else
INSERT INTO table1 (userID, userName) VALUES (@userid, @name)
SET @status = 1

SELECT @status

Answer

Try to put it in BEGIN END

CREATE PROCEDURE save_proc
(
  @userid varchar(10)
  @name varchar(30)
)

AS

DECLARE @status int

  if exists ( SELECT * FROM table1 where userID = @userid AND userName = @name)
    SET @status = 0

    else
    begin
        INSERT INTO table1 (userID, userName) VALUES (@userid, @name)
        SET @status = 1
    end

If you are not putting the BEGIN END then the scope of your else statement is just the next line which is getting executed ie, INSERT INTO table1 (userID, userName) VALUES (@userid, @name) is only under the scope of else block. And SET @status = 1 is outside the scope of else block. So once the else block executes the next query will be executed which is SET @status = 1

On a side note:

When you are checking for if exists then don't use the * wildcard. Instead you can use 1 i.e,

if exists ( SELECT 1 FROM table1 where userID = @userid AND userName = @name)
Comments