1

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

5

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.