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

Answer 2

I also thought about this and couldn't find anything better than this:

BEGIN
  DECLARE
    @p1 int, @p2 int, @p3 int, @p4 int...;

  DECLARE
    @DynamicSQL NVARCHAR(MAX);

  SET
    @p1 = {some logic},
    @p2 = {some different logic},
    @p3 = {another logic},
    @p4 = {yet another logic},
    ...;
  
  
  SET
    @DynamicSQL =
    N'
      some statement
      doing
      somethin
      WHERE
        someColumn = @p1
        AND someAnotherColumn = @p2
        /*no more parameters used below this line*/
    ';

  exec sp_executesql
    @stmt = @DynamicSQL,
    @params = '@p1 int, @p2 int, @p3 int, @p4 int...'
    @p1 = @p1, @p2 = @p2, @p3 = @p3, @p4 = @p4, ...
END;

Notice, that @DynamicSQL uses only 2 out of the 4 possible parameters. Parameters @p1 int, @p2 int, @p3 int, @p4 int... represent the maximum number of parameters you can use in your @DynamicSQL.

You have to have a predefined maximum number of parameters that can be used, and you build the @DynamicSQL statement only with some subset of them. Parameters defined in @params that are not present in the @stmt statement are ignored.

It is not 100 % universal, but I guess that using more than 200 dynamic parameters smells of code smell.

Answer 3

You're trying to work one level too high in abstraction.

Arbitrary parameters requires dynamic SQL, a.k.a. building SQL via strings, which then makes the entire point of parameters moot.

Instead, this should be handled as parameters in the calling code, such as C#, which will allow you to take any SQL statement in a string, apply an arbitrary number of arguments, and execute it.

Answer 4

While this doesn't answer my question I thought it may be useful for others in similar situations. I've discovered the following:

If you have a fixed number of parameters but don't know their names you can pass the parameter values by position rather than name. The following will work:

exec sp_ExecuteSql 
    @sql, 
    @paramdefs, 
    @SelectedUserName, @SelectedJobTitle;

or

exec sp_ExecuteSql 
    @sql, 
    @paramdefs, 
    N'TEST%', N'%Developer%';

Answer 5

Please try this.

Declare @UName varchar(50)
Declare @Job varchar(50)
Set @UName = 'TEST%'
Set @Job = '%Developer%'
exec sp_ExecuteSql @sql, @paramdefs, @UserName = @UName, @JobTitle = @Job;

May this will help you.

Ref From technet.Microsoft.com

Ex.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
                   FROM AdventureWorks2012.HumanResources.Employee 
                   WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';

/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

For dynamic you have to pass something like this

EXECUTE sp_executesql N'Select * from Admin WHERE ID = @ID and FirstName=@FName',
                      N'@ID tinyint, @FName varchar(250)',
                      @ID = 2, @FName = 'admin';