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

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?

CodePudding user response：

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