安全地执行动态 sql 查询

虽然我在评论中已经涵盖了大部分内容，但我觉得值得给出一个答案来给出更多的解释。

首先，正如我所提到的，这不是你应该走的路线。是的，您可以有使用动态 SQL 的过程，但这些过程不应该处理诸如将数据插入表或更新所述数据等基本操作。

使用动态 SQL 时，首先需要确保正确引用动态对象。为此，这并不难，您可以只为对象的架构和名称设置一个参数，然后在注入它们时将它们包装在 .真正的问题来自后者，即“动态”列。QUOTENAME

首先，您似乎需要动态数量的参数;这是一个主要问题。您不能轻而易举地（甚至轻而易举地）参数化动态参数。您也无法将这些参数作为其正确的类型传递;例如，您将无法将 A 作为 A 传递。我可以想象一个使用动态 SQL（是的，我说过两次动态）和对象类型的解决方案，但你应该这样做吗？哈哈如果你知道如何维护这样的解决方案，我一秒钟都不认为你会问你的问题;你会有一些东西正在到达那里，但需要一些帮助。datedatesql_variant

那么，解决方案是什么？好吧，再说一次，就像我在评论中说的那样，您应该为每个表设置单独的过程。您可能还需要单独的 for 和 操作，但您也可以使用单个操作并实现“UPSERT”逻辑;有很多关于如何做到这一点的好文章，所以我不会在这里介绍它。INSERTUPDATE

正如我在评论中提到的，这意味着在更新对象时更新过程。这很正常。当基础表更新为具有更多列时，我会定期更新过程。

同时，应用程序开发人员将需要更新其应用程序代码，以确保将新参数传递给过程。良好的 DevOps 以及 DBA、SQL 开发人员和应用程序开发人员之间的关系是关键，但仅此而已。保持这些沟通渠道的开放和活跃。当您或您的 DBA 在开发环境中更改表、添加新列并修改对象索引（如果需要）并通知您 SQL 开发人员时，您可以执行所需的过程。然后，您可以通知应用程序开发人员，他们可以更新应用程序代码。ALTER

之后，完成内部测试，修复任何错误/意外行为/性能问题，然后进入测试环境。让用户确认它按要求工作，然后将其投入生产。换句话说，遵循良好开发周期的基础知识。

---

TL;DR：你想要的路线是错误的，而且永远不会扩大规模。坚持正常的开发周期，并同步更新数据库和应用程序代码，以便提供新功能。

好吧，所以我想在这里做一些“愚蠢”的事情，我的意思是真正的愚蠢。我想展示这样的实现看起来是多么疯狂，试图实现你真正想要的东西;确实如此。

关于这一点的几点说明：

• 切勿在生产中使用它。
• 永远不要在沙盒以外的任何环境中使用它，试图理解它，以及它是多么愚蠢
• 我只为.我对编写 /Upsert 版本没有兴趣。INSERTUPDATE
• 它一次只处理插入 1 行，不多也不少。
• 切勿在生产中使用它。
• 不，我不会编写支持多行的版本。
• 这用了，我们都知道你永远不应该真正使用它。sql_variant
• 如果您不明白这一点，请不要使用它。
• 切勿在生产中使用它。
• 除了答案中的评论之外，我没有解释这一点。
• 我还必须创建一个函数来获取正确引用的对象名称
  • 因此，它应该支持用户定义的标量数据类型
  • 我还没有测试它是否支持用户定义的标量数据类型
• 它使用是为了让旧版本的用户可以“测试”它。FOR XML PATH
• 我有没有提到，从来没有在生产中使用它？

所以，你有它。我不会支持这个，我没有兴趣支持它，因为你不应该使用它。这只是我想证明这个想法是多么愚蠢的事情。是的。

CREATE DATABASE Demo;
GO
--Creating a new database for an easy "clean up"
USE Demo;
GO
--Single sample table
CREATE TABLE dbo.YourTable (SomeID int IDENTITY(1,1) NOT NULL,
                            SomeDate date NOT NULL,
                            SomeName nvarchar(30),
                            SomeNumber decimal(12,2),
                            EntryDate datetime2(1) NOT NULL DEFAULT SYSUTCDATETIME());

GO
--Create a type for inserting the data into
CREATE TYPE dbo.DataTable AS table (ColumnName sysname NOT NULL,
                                    ColumnValue sql_variant); --Yeah, you saw that right! sql_variant...
GO

--Create a function to return a delimit identified version of a sql_variant's data type
CREATE FUNCTION dbo.QuoteSqlvariant (@SQLVariant sql_variant) 
RETURNS nvarchar(258)
AS 
BEGIN
    RETURN QUOTENAME(CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType'))) +
           CASE WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'char',N'varchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'nchar',N'nvarchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength'))/2,N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'datetime2',N'datetimeoffset',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'decimal',N'numeric',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Precision')),N',',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'varbinary') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'TotalBytes'))-4,N')')
                ELSE N''
           END;
END
GO
--Sample outputs of the function for varying data types
SELECT dbo.QuoteSqlvariant(CONVERT(sql_variant,GETDATE())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,N'Hello')),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,'Goodbye')),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(varbinary(10),N'Hello'))),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(varbinary(7),'Goodbye'))),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,SYSDATETIME())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,SYSDATETIMEOFFSET())),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,1.23)),
       dbo.QuoteSqlvariant(CONVERT(sql_variant,CONVERT(decimal(3,2),1.23)));
GO
--The "solution"
CREATE PROC dbo.CompletelyDynamicInsert @Schema sysname, @Table sysname, @Data dbo.DataTable READONLY, @EXEC nvarchar(MAX) = NULL OUTPUT, @SQL nvarchar(MAX) = NULL OUTPUT AS
BEGIN

    --Let the madness begin
    SET NOCOUNT ON;
    --First we need to create the initial INSERT INTO. This is the "Easy" part...
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    SET @SQL = N'INSERT INTO ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N' (' +
               STUFF((SELECT N',' + QUOTENAME(ColumnName)
                      FROM @Data
                      ORDER BY ColumnName ASC--Ordering is VERY important
                      FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'') + N')' + @CRLF +
               N'VALUES(';

    --Now for the VALUES clause
    SET @SQL = @SQL +
               STUFF((SELECT CONCAT(N',CONVERT(',dbo.QuoteSqlvariant(ColumnValue), N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC),N')',N' COLLATE ' + CONVERT(sysname,SQL_VARIANT_PROPERTY(ColumnValue,'Collation')))
                      FROM @Data
                      ORDER BY ColumnName ASC
                      FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'') + N');'
    --But we need to parmetrise this, so we need to generate a parmeters parameter
    DECLARE @Params nvarchar(MAX);
    SET @Params = STUFF((SELECT CONCAT(N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC), ' ', dbo.QuoteSqlvariant(ColumnValue))
                         FROM @Data
                         ORDER BY ColumnName ASC
                         FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'');
    
    --But, we can't just pass the values from @Data, no... Now we need a dynamic dynamic statement. Oh yay..?
    
    SET @EXEC = N'DECLARE ' + STUFF((SELECT CONCAT(N',@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC), ' ', dbo.QuoteSqlvariant(ColumnValue))
                                    FROM @Data
                                    ORDER BY ColumnName ASC
                                    FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'') + N';';
    SET @EXEC = @EXEC + @CRLF +
                STUFF((SELECT @CRLF + 
                              CONCAT(N'SET ',N'@p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC),N' = (SELECT MAX(CASE WHEN ColumnName = N',QUOTENAME(ColumnName,''''),N' THEN CONVERT(',dbo.QuoteSqlvariant(ColumnValue), N',ColumnValue) END) FROM @Data);')
                       FROM @Data
                       ORDER BY ColumnName ASC
                       FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,N'');

    SET @EXEC = @EXEC + @CRLF + 
                N'EXEC sys.sp_executesql @SQL, @Params,' + 
                STUFF((SELECT CONCAT(N', @p', ROW_NUMBER() OVER (ORDER BY ColumnName ASC))
                       FROM @Data
                       ORDER BY ColumnName ASC
                       FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,N'') + N';';
    
    EXEC sys.sp_executesql @EXEC, N'@SQL nvarchar(MAX), @Params nvarchar(MAX), @Data dbo.DataTable READONLY', @SQL, @Params, @Data;

END;
GO

DECLARE @Data dbo.DataTable;
INSERT INTO @Data (ColumnName,ColumnValue)
VALUES(N'SomeDate',CONVERT(sql_variant,CONVERT(date,'20210101'))), --yes, the insert into this will look dumb like this. YOu need to explicitly convert them all to a sql_variant
      (N'SomeName',CONVERT(sql_variant,N'Larnu')),
      (N'SomeNumber',CONVERT(sql_variant,CONVERT(decimal(12,2),1732.12)));


DECLARE @EXEC nvarchar(MAX),
        @SQL nvarchar(MAX);
EXEC dbo.CompletelyDynamicInsert N'dbo',N'YourTable', @Data, @EXEC OUTPUT, @SQL OUTPUT;

PRINT @EXEC;
PRINT @SQL;
GO

SELECT *
FROM dbo.YourTable;
GO

USE master;
GO

DROP DATABASE Demo;

db<>小提琴