Update table using sp_executesql in SQL Server

问题:

Can anyone help with this case? How to use a dynamic query to insert into table?

DECLARE
    @TypeCode varchar(25),
    @BomDateB varchar(25),
    @BomDateA varchar(25),
    @TbName varchar(25),
    @SQL varchar(max)

SET @TypeCode = 'PS-BPRG15AGW'
SET @TbName = 'z'+@TypeCode 
SET @BomDateB = '8/19/2016'
SET @BomDateA = '8/20/2016'

SET @SQL = 'UPDATE [PMLite].[dbo].['+@TbName+']
            SET [BOM Date] = '+@BomDateA+'
            WHERE [BOM Date] = '+@BomDateB+''

EXEC sp_executesql @SQL

回答1:

This would be the correct approach to do your update:

DECLARE @TypeCode VARCHAR(25)
    , @BomDateB VARCHAR(25)
    , @BomDateA VARCHAR(25)
    , @TbName NVARCHAR(25)
    , @SQL NVARCHAR(MAX);

SET @TypeCode = N'PS-BPRG15AGW';
SET @TbName = N'z' + @TypeCode;
SET @BomDateB = '8/19/2016';
SET @BomDateA = '8/20/2016';

SET @SQL = N'
    UPDATE [PMLite].[dbo].' + QUOTENAME(@TbName) + '
    SET [BOM Date] = CONVERT(NVARCHAR(24), @BomDateA, 121)
    WHERE [BOM Date] = CONVERT(NVARCHAR(24), @BomDateB, 121);';

EXEC sp_executesql @SQL
    , N'@BomDateB VARCHAR(25), @BomDateA VARCHAR(25)'
    , @BomDateB
    , @BomDateA;

Like I've said in comments, do not concat your dynamic SQL when it's not absolutely needed (see @BomDateA and @BomDateB parameters).

And instead of wrapping your text with brackets, use QUOTENAME(). This built-in function is used to wrap your object names into brackets and secures your code from SQL Injection.

Took from documentation:

QUOTENAME (Transact-SQL)

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

回答2:

Your @SQL should be NVARCHAR..

So change like below

Declare @Sql NVARCHAR(4000)

finally there will be some errors in your code ,to avoid conversion issues..so

change dates like

SET [BOM Date] = '+@BomDateA+'

to

SET [BOM Date] = '+''''+CONVERT(nvarchar(24), @BomDateA, 121)+''''+'