SQL 将选择结果作为存储过程的参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14099913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass select result as parameter of stored procedure
提问by GVillani82
I have a T-SQL stored procedure with the following parameters
我有一个带有以下参数的 T-SQL 存储过程
CREATE PROCEDURE [dbo].[SaveData]
-- Add the parameters for the stored procedure here
@UserID varchar(50),
@ServiceID varchar(50),
@param1 varchar(50),
@param2 varchar(50),
@endDate datetime
AS BEGIN
.
.
-- my code --
I want know if it is possible to pass a result of selectas parameter:
我想知道是否可以将结果select作为参数传递：
exec SaveDate (SELECT player.UserID,player.ServiceID, 'no','no',GETDATE()
FROM player)
I tried something like this, but it does not work.
我试过这样的事情，但它不起作用。
采纳答案by MarkD
The SELECT query you wrote in your example would probably bring back multiple rows (your SELECT does not feature a WHERE clause or a TOP(n)). If your intention is to be able to have your procedure engage a "tabular" set of parameters, from SQL Server 2008, you are able to use table valued parameters.
您在示例中编写的 SELECT 查询可能会带回多行（您的 SELECT 没有 WHERE 子句或 TOP(n)）。如果您打算让您的过程使用一组“表格”参数，则从 SQL Server 2008 开始，您可以使用表值参数。
This involves creating a user defined table table and will almost undoubtedly mean adjusting the logic inside the stored procedure.
这涉及创建一个用户定义的表，并且几乎毫无疑问地意味着调整存储过程内部的逻辑。
Hope this helps :)
希望这可以帮助 ：）
See http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspxfor more information.
有关 详细信息，请参阅http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx。
回答by Vladislav
1.One way is:
a) Declare your variables
b) Assign values to them with a single select statement
c) Execute the procedure passing the local variables
d) Execute the following in a loop using WHILE or CURSOR in order to apply this for all rows in TABLE1
1.一种方法是：
a) 声明变量
b) 使用单个 select 语句为它们赋值
c) 执行传递局部变量的过程
d) 使用 WHILE 或 CURSOR 在循环中执行以下操作，以便将其应用于所有TABLE1 中的行
DECLARE @param1 <DATATYPE>, @param2 <DATATYPE>, ...
SELECT TOP 1 @param1 = col1, @param2 = col2, ...
FROM TABLE1
WHERE <where_clause>
EXEC SaveDate @param1, @param2, ...
2.Other way is to define your own table type, fill it, and pass it to procedure. However this requires changing a little bit your stored procedure (in params list your custom type should be followed by READONLY):
2.另一种方法是定义自己的表类型，填充它，然后将其传递给过程。但是，这需要稍微更改您的存储过程（在参数列表中，您的自定义类型应后跟READONLY）：
CREATE TYPE [dbo].[TYPENAME] AS TABLE(
[ID] [int] NOT NULL,
...
)
GO
DECLARE @myTypeVar TYPENAME;
INSERT @myTypeVar
SELECT col1, col2, ...
FROM TABLE1
EXEC SaveData @myTypeVar
