Problem with a dynamic function in SQL Server

Question

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?

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
– Dale K, Commented Mar 11, 2020 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
– sam, Commented Mar 11, 2020 at 20:18
This will never work like this. You can't parameterize object names. — Sean Lange
– Sean Lange, Commented Mar 11, 2020 at 20:22
Try it with your SELECT statement in parentheses. Gets you past "Incorrect syntax near the keyword 'SELECT'." — Nate H
– Nate H, Commented Mar 11, 2020 at 20:22
If you found the answer provide by @DaleK helpful, you should really ACCEPT it meta.stackexchange.com/questions/5234/… — John Cappelletti
– John Cappelletti, Commented Mar 11, 2020 at 21:25

Dale K · Accepted Answer · 2020-03-11 20:31:57Z

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;

edited Mar 11, 2020 at 20:31

answered Mar 11, 2020 at 20:24

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GMB Over a year ago

And if needed, here is a db fiddle for this.

Sean Lange Over a year ago

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 Over a year ago

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].

Collectives™ on Stack Overflow

Problem with a dynamic function in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related