Is it possible to supply sp_ExecuteSql parameter names dynamically?

Question

Is it possible to supply the list of parameters to sp_ExecuteSql dynamically?

In sp_ExecuteSql the query and the parameter definitions are strings. We can use string variables for these and pass in any query and parameter definitions we want to execute. However, when assigning values to the parameters, we cannot seem to use strings or string variables for the parameter names.

For example:

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;

The query @sql, and the parameter definitions, @paramdefs, can be passed into sp_ExecuteSql dynamically, as string variables. However, it seems to me that when assigning values to the parameters we cannot assign dynamically and must always know the number of parameters and their names ahead of time. Note in my example how I could declare parameters @UserName and @JobTitle dynamically and pass in that declaration as a string variable, but I had to explicitly specify the parameter names when I wanted to set them. Is there any way around this limitation?

I would like to be able to both declare the parameters dynamically and assign to them dynamically as well. Something like:

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

Note that this doesn't actually work but illustrates the sort of thing I'd like to happen. If this sort of thing worked then I could pass in different queries with different numbers of parameters which have different names. The whole thing would be dynamic and I wouldn't have to know the names or numbers of parameters beforehand.

Answer 1

You can do this by using a table valued parameter as the only parameter:

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

Now you can simpy insert the parameters into the @Table variable, and they will be present with they original name and type within the query exeuted in the SP_EXECUTESQL. Only make sure you do not use VARCHAR(MAX) or NVARCHAR(MAX) variable types, since they are not supported by SQL_VARIANT. Use (for instance) VARCHAR(4000) instead