named parameters in sp_executesql

Question

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'

Mayor

Answer 1

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'

Fulfill

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! – Mayor

No problem. You can rename them too so it is a little less confusing to read but it works just the same. – Fulfill

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. – Granoff