CODEWITHSUNDEEP
sql-server-2005
Serenity
Serenity

Reputation: 5098

Whats Select '1' for in the following stored proceedure

BEGIN    
 IF EXISTS(SELECT  * FROM Table1 WHERE ID=@ID)      
  BEGIN     
    UPDATE Table1 SET Name=@Name  WHERE ID=@ID    
    SELECT '1'   
  END    
 ELSE     
  SELECT '0'    
END

Is this the row no. of the table or what ? Also "IF EXISTS" is checking what ? the table or if the ID exists or not ??

Upvotes: 0

Views: 117

Answers (4)

bmeding
bmeding

Reputation: 617

The IF EXISTS is checking if there is a row in Table1 with the given ID. If there is a row it will update that row with the given name. The Select "1" will return "1" and Select "0" returns "0". The "1" or "0" would indicate if the row was found or not.

Upvotes: 3

DwB
DwB

Reputation: 38300

Select '1' is used to indicate that Table1 contains the id value @ID (a parameter) was updated. Select '0' indicates that Table1 does not contain the id value @ID.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453142

Presumably some calling code checks this value to determine if a row was updated or not.

Rather than checking and updating (two table accesses) you might as well do this.

 UPDATE Table1 SET Name=@Name  WHERE ID=@ID    
 SELECT CASE WHEN @@Rowcount = 0 THEN 0 ELSE 1 END

If id is the PK then you can just do

 UPDATE Table1 SET Name=@Name  WHERE ID=@ID    
 SELECT  @@Rowcount

Note as long as SET NOCOUNT is not on then the number of rows affected will get passed back to the client application anyway.

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245419

It looks like whoever wrote that Stored Procedure is using that as a return value to indicate success or failure.

Doing things that way will result in a single row with a single column being returned for each call to the procedure.

The correct way to handle this would be to actually use the return value of the stored procedure, rather than returning the single column single row:

BEGIN
    IF EXISTS(SELECT * FORM Table1 WHERE ID = @ID)
    BEGIN
        UPDATE Table1 SET Name = @Name WHERE ID = @ID
        RETURN 1
    END

    RETURN 0
END

Upvotes: 4

Related Questions