2

I have a table dbo.t_products and I want to know the last record updated. For that I have an attribute last_changed which stores for each record the timestamp of the last update.

Finally I want to save that result in a variable called @y. 

DECLARE @y DATETIME
DECLARE @p_table VARCHAR(100)

SET @p_table = 'dbo.t_products'

EXECUTE sp_executesql N'SET @y = SELECT MAX(last_changed) FROM @p_table'
                        ,N'@p_table VARCHAR, @y DATETIME OUTPUT'
                        ,@p_table
                        ,@y OUTPUT
SELECT @y

The system returns the following message:

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'SELECT'.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marco
  • 21
  • 1
  • 3
    The first step for debugging dynamic SQL is to print the SQL you are trying to execute - then the reason for the error will normally become obvious. – Dale K Mar 11 '20 at 20:17
  • I am not sure I clearly followed the statement `EXECUTE sp_executesql ....` but one obvious thing that I see is, @p_table is repeated. Could you just mention plain statement (like what would be resultant dynamic statement) – sam Mar 11 '20 at 20:18
  • 6
    This will never work like this. You can't parameterize object names. – Sean Lange Mar 11 '20 at 20:22
  • Try it with your SELECT statement in parentheses. Gets you past "Incorrect syntax near the keyword 'SELECT'." – Nate H Mar 11 '20 at 20:22
  • You have to add table name by string concatenation. Also, do you mean `SELECT @y=MAX(last_changed) FROM @p_table`? This post may help: https://stackoverflow.com/a/7246646/3218693 – Bill Huang Mar 11 '20 at 21:04
  • 1
    If you found the answer provide by @DaleK helpful, you should really ACCEPT it https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – John Cappelletti Mar 11 '20 at 21:25

1 Answers1

4

The whole point of using dynamic SQL in your case (I assume) is to allow the use of a dynamic table name. In which case you have to insert the table name into the dynamic SQL string - you can't pass it in as a parameter, thats the problem you are trying in the first place.

Also you don't need a SET followed by a SELECT just use SELECT directly to set the variable.

Finally you definitely want to use the QUOTENAME function to escape your table name and avoid an SQL injection attack - this requires you split the table name and schema name.

DECLARE @y DATETIME;
DECLARE @p_schema VARCHAR(100);
DECLARE @p_table VARCHAR(100);
DECLARE @SQL NVARCHAR(max);

SET @p_schema = 'dbo';
SET @p_table = 't_products';
-- Add the table name to the dynamic SQL 
SET @SQL = 'SELECT @y = MAX(last_changed) FROM ' + QUOTENAME(@p_schema) + '.' + QUOTENAME(@p_table);

EXECUTE sp_executesql @SQL, N'@y DATETIME OUTPUT', @y OUTPUT;

-- PRINT(@SQL); --- Debugging

SELECT @y;
Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 2
    And if needed, [here is a db fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=93e1eff4148cea80d88389b667c3e44f) for this. – GMB Mar 11 '20 at 20:28
  • 1
    I would suggest an adjustment to this fine answer. The OP should use two parameters. One for the schema and another for the table name. That way you can wrap both of them with QUOTENAME to mitigate the risk of sql injection. – Sean Lange Mar 11 '20 at 20:29
  • 1
    Dale I see you added QUOTENAME. The problem is the OP has dbo.TableName and quotename will not work there. it would return [dbo.TableName]. – Sean Lange Mar 11 '20 at 20:30