How to pass Table as an argument in Function or Stored Procedure

Question

How to pass Table as an argument in Function or Stored Procedure

Vasant Raj

SSCommitted

Points: 1835
More actions
May 30, 2005 at 12:45 am

#66287

hi,
i m able to return a TABLE thru Function.
Now, i need to pass TABLE as an argument to a Function or Stored procedure.
How would i manipulate it in the function/stored proc.
OR is it possible to pass different arguments in the same Function/Stored Procedure.
thnx.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

You can't do that, AFAIK.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Vasant Raj SSCommitted Points: 1835 More actions · Answer 2

OK

Any other method which i can use for the multiple arguments...

Thru XML ????

i need to create a STORED PROC which will convert its arguments into INSERT's, UPDATES, DELETES...

The arguments are not certain...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Sounds like dynamic sql task here... What problem are you trying to solve?

gurusql Mr or Mrs. 500 Points: 508 More actions · Answer 4

Frank is right of course, you cannot do this. However, when presented with this problem, I have used the following solution in the past. First create a table with all of the fields that you wanted to pass into the stored procedure and then add one more field (I called mine the TokenId). Add all of the data that you want passed into the procedure with the same TokenId. Have the stored procedure have an input parameter with that same TokenId. The procedure can then query the table retrieve the data and when it is finished processing the data it can be deleted from this table by the procedure. This lets you have multiple version of this procedure running at the same time without the table getting to large.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Maybe you get some additional ideas here: http://www.sommarskog.se/share_data.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Marts SSC Eights! Points: 937 More actions · Answer 6

I often do this by sending my data in as a delimited list, eg with commas etc.

Then I have a UDF that is called SplitString, which returns a table from the string.

Martin

Vasant Raj SSCommitted Points: 1835 More actions · Answer 7

Vasant Raj

SSCommitted

Points: 1835

May 31, 2005 at 6:43 am

#561858

i m also doing the same thing...

thnx...