locked
Basic stored procedure syntax question RRS feed

  • Question

  • Dear All,

    I have a stored procedure which should return the records in a table for a given record Count (3) in this case but it does work the way I  had thought. Any help would be appreciated:

    As I said in the title, it is a basic syntax question! Thanks.

    Alastair

    ALTER  PROC sp_List_Differences
    (
    @TotRecs int Output
    )
    AS
    BEGIN
     SELECT @TotRecs =  COUNT(ID) FROM dbo.NLL_3gDifferences

     IF @TotRecs = 3
      RETURN 0
     ELSE
      RETURN
      SELECT * FROM dbo.NLL_3gDifferences
    END
    GO

    EXEC sp_List_Differences

    Server: Msg 201, Level 16, State 4, Procedure sp_List_Differences, Line 0
    Procedure 'sp_List_Differences' expects parameter '@TotRecs', which was not supplied.

    Thursday, November 24, 2011 4:19 PM

Answers

  • See few changes:

    ALTER  PROC spList_Differences -- never use sp_ prefix for your own stored procedures unless you want them to be created as system and mark them as such
    (
    @TotRecs int Output
    )
    AS
    BEGIN
     SELECT @TotRecs =  COUNT(ID) FROM dbo.NLL_3gDifferences
    
     IF @TotRecs = 3
      RETURN 0 
     ELSE 
      
      SELECT * FROM dbo.NLL_3gDifferences
    END
    GO
    ------------
    
    declare @Recs int;
    EXEC spList_Differences @TotRecs = @Recs OUTPUT;
    
    SELECT @Recs;

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Thursday, November 24, 2011 4:34 PM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 8:50 PM
    Thursday, November 24, 2011 4:28 PM
  • EXEC sp_List_Differences

    Server: Msg 201, Level 16, State 4, Procedure sp_List_Differences, Line 0
    Procedure 'sp_List_Differences' expects parameter '@TotRecs', which was not supplied.


    You need to pass the @TotRecs parameter as an output parameter.  Try:

     

    DECLARE @TotRecs int;
    EXEC usp_List_Differences @TotRecs = @TotRecs output;
    


    Also, do not name stored procedures with the 'sp_' prefix as that is reserved from system procs.  Consider 'usp_' instead.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 8:50 PM
    Thursday, November 24, 2011 4:30 PM

All replies

  • See few changes:

    ALTER  PROC spList_Differences -- never use sp_ prefix for your own stored procedures unless you want them to be created as system and mark them as such
    (
    @TotRecs int Output
    )
    AS
    BEGIN
     SELECT @TotRecs =  COUNT(ID) FROM dbo.NLL_3gDifferences
    
     IF @TotRecs = 3
      RETURN 0 
     ELSE 
      
      SELECT * FROM dbo.NLL_3gDifferences
    END
    GO
    ------------
    
    declare @Recs int;
    EXEC spList_Differences @TotRecs = @Recs OUTPUT;
    
    SELECT @Recs;

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Thursday, November 24, 2011 4:34 PM
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 8:50 PM
    Thursday, November 24, 2011 4:28 PM
  • EXEC sp_List_Differences

    Server: Msg 201, Level 16, State 4, Procedure sp_List_Differences, Line 0
    Procedure 'sp_List_Differences' expects parameter '@TotRecs', which was not supplied.


    You need to pass the @TotRecs parameter as an output parameter.  Try:

     

    DECLARE @TotRecs int;
    EXEC usp_List_Differences @TotRecs = @TotRecs output;
    


    Also, do not name stored procedures with the 'sp_' prefix as that is reserved from system procs.  Consider 'usp_' instead.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Tuesday, November 29, 2011 8:50 PM
    Thursday, November 24, 2011 4:30 PM