将select结果作为存储过程的参数传递

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 select as parameter:

我想知道是否有可能通过选择的结果作为参数:

    exec SaveDate (SELECT player.UserID,player.ServiceID, 'no','no',GETDATE()
           FROM player)

I tried something like this, but it does not work.

我试过这样的方法，但没用。

2 个解决方案

#1

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).aspx for more information.

更多信息请参见http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx。

#2

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或游标在一个循环中执行以下操作，以便将其应用于表1中的所有行。

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。另一种方法是定义自己的表类型，填充它，并将其传递给过程。但是，这需要稍微修改一下存储过程(在params列表中，您的自定义类型应该后面跟着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

#1