sql - 是否可以动态提供 sp_ExecuteSql 参数名称？

Question

是否可以动态地向 sp_ExecuteSql 提供参数列表？

在 sp_ExecuteSql 中，查询和参数定义是字符串。我们可以为这些使用字符串变量，并传入我们想要执行的任何查询和参数定义。但是，在为参数赋值时，我们似乎不能使用字符串或字符串变量作为参数名称。

例如:

DECLARE @SelectedUserName NVARCHAR(255) ,
    @SelectedJobTitle NVARCHAR(255);
SET @SelectedUserName = N'TEST%';
SET @SelectedJobTitle = N'%Developer%';

DECLARE @sql NVARCHAR(MAX) ,
    @paramdefs NVARCHAR(1000);
SET @sql = N'select * from Users where Name LIKE @UserName '
    + N'and JobTitle LIKE @JobTitle;'
SET @paramdefs = N'@UserName nvarchar(255), @JobTitle nvarchar(255)';
EXEC sp_ExecuteSql @sql, @paramdefs, @UserName = @SelectedUserName,
    @JobTitle = @SelectedJobTitle;

查询@sql 和参数定义@paramdefs 可以作为字符串变量动态传递到sp_ExecuteSql 中。但是，在我看来，在为参数赋值时，我们不能动态赋值，必须始终提前知道参数的数量及其名称。请注意在我的示例中我如何动态声明参数 @UserName 和 @JobTitle 并将该声明作为字符串变量传递，但是当我想设置它们时，我必须明确指定参数名称。有没有办法绕过这个限制？

我希望能够动态声明参数并动态分配给它们。就像是:

EXEC sp_ExecuteSql @sql, @paramdefs,
    N'@UserName = @SelectedUserName, @JobTitle = @SelectedJobTitle';

请注意，这实际上不起作用，但说明了我希望发生的事情。如果这种事情有效，那么我可以使用不同数量的具有不同名称的参数传递不同的查询。整个过程都是动态的，我不必事先知道参数的名称或数量。

Answer 1

您可以通过使用表值参数作为唯一参数来执行此操作:

DECLARE @YourQuery NVARCHAR(MAX0 = '<your dynamic query>'

CREATE TYPE dbo.SqlVariantTable AS TABLE
(
    [Name]  VARCHAR(255),
    Type    VARCHAR(255),
    Value   SQL_VARIANT
)

DECLARE @Table SqlVariantTable;

-- Insert your dynamic parameters here:
INSERT INTO @Table 
VALUES
    ('Parameter1', 'VARCHAR(255)', 'some value'),
    ('Parameter2', 'INT', 3),

DECLARE @ParameterAssignment NVARCHAR(MAX)
SELECT @ParameterAssignment = ISNULL(@ParameterAssignment + ';','') + 'DECLARE ' + Name + ' ' + Type + ' = (SELECT CAST(Value AS ' + Type + ') FROM @p1 WHERE Name = ''' + Name + ''')'
FROM @Table

SET @YourQuery = @ParameterAssignment + ';' + @YourQuery

EXEC SP_EXECUTESQL @YourQuery, N'@p1 SqlVariantTable READONLY', @Table

现在您可以简单地将参数插入@Table 变量中，它们将在 SP_EXECUTESQL 中执行的查询中以原始名称和类型显示。仅确保您不使用 VARCHAR(MAX) 或 NVARCHAR(MAX) 变量类型，因为 SQL_VARIANT 不支持它们。使用(例如)VARCHAR(4000) 代替