5

is there any way that you can call sp_executesql with parameters that don't depend in the order they are defined in the store? the same query with exec works well, and if you have the same order it also works well, but it's a pain having to match the parameter one by one, because sometime I am generatin the call dynamically with helpers, and if the dto object don't have the same fields in the same order, doesn't work well.

create procedure ordertest
  @PARAM1 INT,
  @PARAM2 INT
  AS 
BEGIN
SELECT @PARAM1 AS ONE, @PARAM2 AS TWO
END

-- this works
EXEC ordertest @PARAM1 = 1, @PARAM2 = 2
exec sp_executesql N'exec ordertest @PARAM1, @PARAM2', N'@param1 int, @param2 int', @param2 = '2',  @param1 =  '1'
EXEC ordertest @PARAM2 = 2, @PARAM1 = 1

-- this doesn't work
exec sp_executesql N'exec ordertest @PARAM2, @PARAM1', N'@param1 int, @param2 int', @param2 = '2',  @param1 =  '1'
Jokin
  • 4,188
  • 2
  • 31
  • 30

1 Answers1

8

Sure you can do this. You just need to add which parameter is which when you call it.

exec sp_executesql N'exec ordertest @PARAM2 = @Param2, @PARAM1 = @Param1', N'@param1 int, @param2 int', @param2 = '2',  @param1 =  '1'
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Wow, that was so easy that I'm ashamed. With the parameters already there I didn't get that those ones were getting replaced and the equivalent call was to "exec ordertest 2, 1". Thank You! – Jokin Feb 11 '15 at 15:14
  • No problem. You can rename them too so it is a little less confusing to read but it works just the same. – Sean Lange Feb 11 '15 at 15:17
  • Just wanted to add a note saying that while this looks confusing, it does appear to be the correct way to combine named parameters with parameterized queries. For example, nLog allows you to provide command text to run to insert log entries, and `exec ProcName @Param1=@Param1, @Param2=@Param2` is the way to do that. `sp_executesql` substitutes those parameter values correctly. Thank you for the answer. – Nick Jun 26 '15 at 21:50