[SOLVED] How to fix "Must declare the scalar variable" issue in sql server dynamic query?

How to fix "Must declare the scalar variable" issue in sql server dynamic query?

I am trying to execute this statement in sql server:

EXECUTE ('SELECT @lnRowIdMin = MIN(TMP_ROW_ID) FROM  #' + @lvcBaseTable).

Here @lnRowIdMin is declared as an integer variable and @lvcBaseTable is declared as an varchar(255) variable in my original code.

Executing the original code throws the error for the above statement:

Must declare the scalar variable

Then I changed the statement to:

EXECUTE ('SELECT ' + @lnRowIdMin + ' = MIN(TMP_ROW_ID) FROM  #' + @lvcBaseTable).

Now it throws this error: "Incorrect syntax near '='"

Solution

At a total guess, @lnRowIdMin is declared outside of the dynamic SQL, and needs to have the value assigned. You therefore need to parametrise your SQL, and use an OUTPUT parameter:

--Prior stuff here, including declaration of @lnRowIdMin

DECLARE @SQL nvarchar(MAX),
        @Param nvarchar(MAX);
SET @SQL = N'SELECT @lnRowIdMin = MIN(TMP_ROW_ID) FROM  ' + QUOTENAME(N'#' + @lvcBaseTable) + N';';
SET @Param = N'@lnRowIdMin int OUTPUT'; --Guessed datatype

EXEC sp_executesql @SQL, @Param, @lnRowIdMin = @lnRowIdMin OUTPUT;