0

I am trying to make a query that I can run from Python with dynamic table name and date. In the process of this, I have tried the following query in SSMS, but it is producing an error message. How can I use variables for table name and a date, and get the query to work?

DECLARE @table_name VARCHAR(50)='table_name';
DECLARE @valid_to datetime = getdate();

EXEC('UPDATE '+ @table_name + '
SET valid_flag = 0, 
valid_to = '+ @valid_to +'
where valid_flag=1')

I get the following error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '10'

TT.
  • 15,774
  • 6
  • 47
  • 88
dagrun
  • 621
  • 3
  • 11
  • 29
  • 1
    You are missing a space after `UPDATE`. – Peter B Aug 10 '18 at 10:48
  • 1
    Besides a lot of other flaws this comes quite obviously from here: `valid_to = '+ @valid_to +'`. Your `@valid_to` is declared as `datetime` and cannot be part of a string concatenation by `+`. But a `CAST` won't fix this (dateformat, quotes...) The correct solutions comes from Gordon: Use parameters and use `QUOTENAME()`! (or [meet bobby tables](http://bobby-tables.com/)) – Shnugo Aug 10 '18 at 11:11

1 Answers1

3

Use sp_executesql and parameters:

DECLARE @table_name VARCHAR(50) = 'table_name';
DECLARE @valid_to datetime = getdate();

DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ @table_name + N'
    SET valid_flag = 0, 
        valid_to = @valid_to
    WHERE valid_flag = 1
';

EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;

EDIT:

As recommended by Larnu a comment:

DECLARE @table_name sysname = 'table_name';
DECLARE @valid_to datetime = getdate();

DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ QUOTENAME(@table_name) + N'
    SET valid_flag = 0, 
        valid_to = @valid_to
    WHERE valid_flag = 1
';

EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    I'd suggest `sysname` over `varchar(50)`, incase the OP does indeed have an object with a longer name. You should definitely make use of `QUOTENAME` though, SQL injection is not your friend. But at least the `WHERE` is parametrised. – Thom A Aug 10 '18 at 10:44