0

I am trying to create a stored procedure with dynamic SQL inside of it. The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part. I keep getting this error on the 2 last variables:

Msg 137, Level 15, State 2, Line 34 Must declare the scalar variable "@numOfOffset". Msg 137, Level 15, State 2, Line 34 Must declare the scalar variable "@numOfRows".

I will appreciate some help, please

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain = 
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy + 
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET @numOfOffset - 1 ROWS FETCH NEXT @numOfRows ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END

closed as off-topic by MDCCL, Marcello Miorelli, Michael Green, hot2use, Jon of All Trades Jun 24 at 20:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – MDCCL, Marcello Miorelli, Michael Green, hot2use, Jon of All Trades
If this question can be reworded to fit the rules in the help center, please edit the question.

4

You're not declaring either @numOfOffset or @numOfRows variables within the context of your Dynamic SQL statement. If you copy the output of your PRINT @qaeryMain statement, you'll notice the distinct lack of any DECLARE statements, this is the reason why you're getting the error.

If you just change your code to the following, it should resolve your issue as it will specify literals within your DSQL statement instead of undefined variables.

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain = 
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy + 
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET ' + CAST(@numOfOffset AS VARHCAR(50)) + ' - 1 ROWS FETCH NEXT ' + CAST(@numOfRowsAS VARHCAR(50)) + ' ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END

Not the answer you're looking for? Browse other questions tagged or ask your own question.