asked
passing table content via stored procedure parameters
I'm using SQL Server 2008. I need to be able to pass table content via stored procedure parameters. Example: MyTable (col1, col2) has the following data: 1, 'A' 2, 'B' 3, 'C' Stored procedure: sp_read_tbl (@p1_col1 int OUT, @p1_col2 char(1) OUT, @p2_col1 int OUT, @p2_col2 char(1) OUT, @p3_col1 int OUT, @p3_col2 char(1) OUT) How can I do this inside a stored procedure? Any help is appreciated.
I still don't understand what you are trying to do. You have a table that looks like: col1 col2 1 A 2 B 3 C What are you wanting to do with the table?
Hi - I need to pass the content of the table to the calling program via stored procedure parameters. How to do it so that the stored procedure would return back the following parameters: @p1_col1 = 1 @p1_col2 = 'A' @p1_col1 = 2 @p1_col2 = 'B' @p1_col1 = 3 @p1_col2 = 'C' Thank you.
Sorry for the confusion. the SP parameters will be OUT parameters. The calling program needs a 'dump' of the content of the table. in this example, 3 records of the 2-column table, each element returned to the calling program in a parameter. Hope this helps clarify.
So if you have a table with r rows and c columns, you want to return r x c output parameters, using an example of 3 rows and 2 columns ordered r1c1, r1c2, r2c1, r2c2, r3c1, r3c2? yes, that's correct.
We've got some interesting answers to do something that stored procedures were never meant to do. If you want a result set from a stored procedure, you should just write a SELECT statement inside of it. What is the outside factor that is forcing you to return multiple rows of data via parameters?
The first example in the Books Online topic *Using a Stored Procedure with Output Parameters* covers this scenario for a single output parameter -
https://msdn.microsoft.com/en-us/library/ms378108%28v=sql.100%29.aspx It should be fairly simple to extend that to your additional parameters.
Take a look at table valued parameters:
https://msdn.microsoft.com/en-us/library/bb510489%28v=sql.100%29.aspx They can be a performance killer though. It depends on your circumstances. TBH though, this seems bit of a roundabout way to do the work. Why not just do the processing on the table itself and then select the output from that?
Thank you for the answer above. Yes, a little strange, but it's a restriction of the calling app. Not sure if table valued pararmeters would work, since I have to pass the values in individual parameters in the stored procedure. Is there any way to do this via cursor loop fetch?
I'm still a bit confused. Are you saying that you want to pass in a set of parameters, row by row from a table, but return a single resultset containing all of the rows?
Moved this to comment to keep it together: "Sorry for the confusion. the SP parameters will be OUT parameters. The calling program needs a 'dump' of the content of the table. in this example, 3 records of the 2-column table, each element returned to the calling program in a parameter. Hope this helps clarify. " So if you have a table with r rows and c columns, you want to return r x c output parameters, using an example of 3 rows and 2 columns ordered r1c1, r1c2, r2c1, r2c2, r3c1, r3c2?
Ok, so I was curious as to whether this was even possible. The only way to achieve what you want that I can see is to dynamically generate the stored procedure, as you have no way of knowing how many output parameters you will need before runtime. I've attached a file here: [link text][1] [1]: /storage/temp/2179-proccreatesql.txt This creates a stored procedure that will dynamically create a stored procedure for any table given as input. It also outputs a sql statement that can be used to execute the proc and return the values from it. I've added a debug parameter, setting this to 1 will print the text for the stored procedure to be created. The procedure will return the parameters in clustered index order if one exists, otherwise it orders by the first column. Note that a stored procedure can have a maximum of 2100 parameters, so if r X c > 2100 it won't work. Example: ---------------------- -- set up source table ---------------------- create table dbo.MySource (Col1 int, Col2 char(1)) go insert dbo.MySource values (1,'A'),(2,'B'),(3,'C') go ------------------------------------------------------ -- execute with debug to show generated procedure text ------------------------------------------------------ exec dbo.CreateTableToParameters @source_table = 'dbo.MySource' , @exec_statement = null , @debug = 1 -- generated procedure text: create procedure dbo.TableToParameters ( @r1c1 int output ,@r1c2 char(1) output ,@r2c1 int output ,@r2c2 char(1) output ,@r3c1 int output ,@r3c2 char(1) output ) as begin ; with cte as ( select rn = row_number() over (order by Col1) , Col1 , Col2 from dbo.MySource ) select @r1c1 = max(case when rn = 1 then Col1 else null end) , @r1c2 = max(case when rn = 1 then Col2 else null end) , @r2c1 = max(case when rn = 2 then Col1 else null end) , @r2c2 = max(case when rn = 2 then Col2 else null end) , @r3c1 = max(case when rn = 3 then Col1 else null end) , @r3c2 = max(case when rn = 3 then Col2 else null end) from cte end ------------------- -- execute for real ------------------- declare @exec_statement_ret varchar(max) exec dbo.CreateTableToParameters @source_table = 'dbo.MySource' , @exec_statement = @exec_statement_ret output , @debug = 0 select @exec_statement_ret -- returned from proc declare @r1c1_ret int ,@r1c2_ret char(1) ,@r2c1_ret int ,@r2c2_ret char(1) ,@r3c1_ret int ,@r3c2_ret char(1) exec dbo.TableToParameters @r1c1 = @r1c1_ret output , @r1c2 = @r1c2_ret output , @r2c1 = @r2c1_ret output , @r2c2 = @r2c2_ret output , @r3c1 = @r3c1_ret output , @r3c2 = @r3c2_ret output select @r1c1_ret , @r1c2_ret , @r2c1_ret , @r2c2_ret , @r3c1_ret , @r3c2_ret -- output when return executed /* ----------- ---- ----------- ---- ----------- ---- 1 A 2 B 3 C */
18 People are following this question.
