How to properly update a dateTime value using a dynamic query in SQL Server

Question

I have a table called Applications that has a column called StatusChangeDate that is of type datetime. I am trying to use a dynamic query to update the StatusChangeDate.

My initial query was as follows:

DECLARE @TableName NVARCHAR(MAX) = (SELECT TOP 1 name FROM sys.Tables WHERE Name LIKE '%AuthorizedUsers_%');
DECLARE @UTCDate DATETIME = GETUTCDATE();

DECLARE @ApplicationsTableUpdateQuery NVARCHAR(100) = '
    UPDATE A
    SET StatusChangeDate = ' + @UTCDate + '
    FROM [MyDb_1].[dbo].[Applications] A
    INNER JOIN [MyDb_2].[dbo].[' + @TableName +'] A2
        ON A.ApplicationId = A2.ApplicationId
'
EXEC sp_executesql @ApplicationsTableUpdateQuery

That gave me the following error: "Conversion failed when converting date and/or time from character string."

After I did some research, what I found was that I needed to convert my @UTCDate variable to a VARCHAR, and my updated query was as follows:

DECLARE @ApplicationsTableUpdateQuery NVARCHAR(100) = '
    UPDATE A
    SET StatusChangeDate = ' + CONVERT(VARCHAR(100), @UTCDate) + '
    FROM [MyDb_1].[dbo].[Applications] A
    INNER JOIN [MyDb_2].[dbo].[' + @TableName +'] A2
        ON A.ApplicationId = A2.ApplicationId
'
EXEC sp_executesql @ApplicationsTableUpdateQuery

But that gave me a syntax error that said: "Incorrect syntax near '19'" which I think is the format that @UTCDate since today is the 19th of march.

I am confused about how would I solve this. Could someone shed some light on what I am doing wrong and how to properly do this?

You need to pass it as a proper parameter. `SET @sql = N'UPDATE … SET col = @param …'; EXEC sys.sp_executesql @sql, N'@param datetime', @param;';` you shouldn’t be concatenating params into your SQL unless you have to, and you should be using QUOTENAME() around object names instead of adding square brackets yourself. This is how SQL injection happens. See https://sqlblog.org/dynamic-sql — Aaron Bertrand, Mar 19 '22 at 23:37
@AaronBertrand Could you write the query in an answer so it might be easier to read and understand, please? I am still new to the SQL stuff and it would be helpful. — Hussam Ahmed, Mar 19 '22 at 23:56
@HussamAhmed People "new to the SQL stuff" probably should not be attempting dynamic sql. Regardless, you can't debug what you cannot see. You need to look at the statement you generate to understand any errors that occur when you execute. Most likely you would have seen the obvious problem - you needed to wrap your converted datetime constant in single quotes. But why bother with that? Just set the column using the GetUTCDate function directly in the statement. Why set a variable and then embed / pass the variable in the statement? — SMor, Mar 20 '22 at 00:24
And there are a number of bad habits you have learned. These will only make writing / using SQL more difficult over time. — SMor, Mar 20 '22 at 00:25

score 1 · Accepted Answer · answered Mar 19 '22 at 23:59

Parameterise your query properly and pass the datetime value as a parameter to the query, you will need to concatenate table name anyway, Try this

DECLARE @TableName NVARCHAR(MAX) = (SELECT TOP 1 name FROM sys.Tables WHERE Name LIKE '%AuthorizedUsers_%');
DECLARE @UTCDate DATETIME = GETUTCDATE();

DECLARE @ApplicationsTableUpdateQuery NVARCHAR(MAX) 
= N'
    UPDATE A
    SET StatusChangeDate = @UTCDate 
    FROM [MyDb_1].[dbo].[Applications] A
    INNER JOIN [MyDb_2].[dbo].' + QUOTENAME(@TableName) + N' A2
        ON A.ApplicationId = A2.ApplicationId
'
EXEC sp_executesql @ApplicationsTableUpdateQuery
    , N'@UTCDate DATETIME'
    , @UTCDate

Do you know what I should look for if I wanted to read more on how to pass parameters into dynamic queries similar to this case? — Hussam Ahmed, Mar 20 '22 at 00:09
@HussamAhmed read and learn about using parameterised dynamic sql with sp_executesql — M.Ali, Mar 20 '22 at 00:11

How to properly update a dateTime value using a dynamic query in SQL Server

1 Answers1