Is it possible to supply sp_ExecuteSql parameter names dynamically?
sql, sql-server, t-sql
Solution
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.
Problem
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.