Pass select result as parameter of stored procedure

Question

I have a T-SQL stored procedure with the following parameters

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.

Answer 1

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

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):

CREATE TYPE [dbo].[TYPENAME] AS TABLE(     [ID] [int] NOT NULL,     ... ) GO  DECLARE @myTypeVar TYPENAME;  INSERT @myTypeVar SELECT col1, col2, ... FROM TABLE1  EXEC SaveData @myTypeVar 

Answer 2

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.

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.