Whats Select '1' for in the following stored proceedure

Question

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 ??

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.