sp_executesql中的命名参数 [英] named parameters in sp_executesql

问题描述

有什么方法可以使用不依赖于它们在存储中定义的顺序的参数来调用sp_executesql?使用exec执行相同的查询效果很好，并且如果您使用相同的顺序也可以很好地使用效果，但是必须将参数一一匹配是一件很痛苦的事情，因为有时我会通过助手动态地进行调用，并且如果dto对象不起作用没有相同顺序的相同字段，效果不佳.

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'

推荐答案

当然可以.您只需在调用时添加哪个参数即可.

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'

这篇关于sp_executesql中的命名参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！