-3

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 '='"

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
user11668855
  • 3
  • 1
  • 1
  • 2
  • `'...#' + @lvcBaseTable` implies you're trying to reference a temporary table. You can only reference a temporary table in the scope it was declared; and dynamic SQL is run in a different scope. You'll need to use a persisted table and properly quote your object name. Also is @lnRowIdMin meant to be a column alias, or variable assignment? I can't tell, as you seem to think your 2 variables will work differently. If the former, again, you need to ensure you quote it. if the latter, you need to parametrise the SQL with an `OUTPUT` parameter. There's not enough detail to properly answer this. – Thom A Jun 19 '19 at 08:26
  • Why does *what* work? What is *"this"* – Thom A Jun 19 '19 at 08:29
  • I just have one question, why does this work: **`EXECUTE ('TRUNCATE TABLE ' + @lvcDbName + '..' + @lvcBackUpTable)`** These variables(@lvcDbName and @lvcBackUpTable) are also not declared inside the scope of dynamic query. But it works. And this doesn't: **`EXECUTE ('SELECT ' + @lnRowIdMin + ' = MIN(TMP_ROW_ID) FROM #' + @lvcBaseTable)`** I am new to SQL. Sorry for this silly questions. Thanks for your response. – user11668855 Jun 19 '19 at 08:32
  • Because you're injecting them @user11668855, and leaving yourself open to SQL Injection. Without knowing the value of `@lnRowIdMin` i don't know what your second statement derives to, but it generates an error. You need to explain what your goal is here, and then we can show you how you achieve it. All we have are 2 snippets of code, neither of which work, for different reasons. But what we don't know is what you're trying to achieve. – Thom A Jun 19 '19 at 08:39

2 Answers2

0

When executing dynamic SQL it si executed in different scope, so if you declare your variables in the same scope as varchar bariable with SQL statement, you will get such error.

Correct way of doing would be:

EXECUTE ('DECLARE @lnRowIdMin INT; SELECT @lnRowIdMin = MIN(TMP_ROW_ID) FROM  #' + @lvcBaseTable)

But then you are unable to access this variable from outside the dynamic SQL,which is what you want, I think.

Two ways to resolve this:

  1. Put code which uses the variable in dynamic SQL as well.
  2. Insert the value in some temporary table, then you can use it outside dynamic SQL just be executing SELECT against the temporary table.
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • As the OP is referencing a temporary table, this still won't work; as it won't exist within the scope of the dynamic statement.. The correct way would be to parametrise this though and not inject raw string in a dynamically generated statement. SQL Injection is *still* a huge problem and this is certainly not the correct way of doing it. – Thom A Jun 19 '19 at 08:19
  • @Larnu You can decare temp table which will be seen outside fo the scope. SQL injection? I think this is executed from SSMS and if somebody is already using SSMS, then that person don't need to use injection. – Michał Turczyn Jun 19 '19 at 08:26
  • Those are poor assumptions to make; injection should never be allowed. – Thom A Jun 19 '19 at 08:32
0

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75