question

learningSQL avatar image
learningSQL 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.
sqlsql-serversqlserver 2008
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

P90Actuary avatar image P90Actuary commented ·
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?
0 Likes 0 ·
learningSQL avatar image learningSQL commented ·
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.
0 Likes 0 ·
learningSQL avatar image learningSQL commented ·
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.
0 Likes 0 ·
learningSQL avatar image learningSQL commented ·
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.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
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?
0 Likes 0 ·
Show more comments
KenJ avatar image
KenJ answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
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?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

learningSQL avatar image learningSQL commented ·
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?
0 Likes 0 ·
iainrobertson avatar image iainrobertson learningSQL commented ·
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?
0 Likes 0 ·
iainrobertson avatar image iainrobertson commented ·
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?
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
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 */

proccreatesql.txt (9.3 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.