Using Table variables as inputs for stored procedure''s?

Question

Using Table variables as inputs for stored procedure''s?

Regan Galbraith

SSCertifiable

Points: 5227
More actions
June 14, 2005 at 11:42 pm

#164936

Can table variable be used as output or input variables for stored procedures? I can find anything in BOL, or Ken Henderson's "Guru's Guide to .." books, or google (so far).
CREATE PROCEDURE [dbo].UP_ExtractPostCard_Card
@PostCardExtractCardResultSet TABLE
  ([ID]   INTEGER IDENTITY(1,1),
  [DataLine]  varchar(110))
gives a syntax error near TABLE, but the table declaration is fine:
DECLARE @PostCardExtractCardResultSet TABLE
  ([ID]   INTEGER IDENTITY(1,1),
  [DataLine]  varchar(110))
Works fine..
Anyone?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Tom Pearson-222242 SSC Journeyman Points: 91 More actions · Answer 1

If you want to use the table within the stored procedure I would suggest creating it as a temporary table and running through it, rather than passing it in (if this is possible)

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

Thanks Tom,

I was trying to avoid Temp table in favour of Table variables. I have found a way to do what I want with a UDF, and on re-looking, may have over-complicated the whole thing 🙂

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

You cannot use it directly as an array or table.

Workaround is cocatinate the values with usually '|' and split it in the stored procedure to avail it as a table.

Remi has posted a function which wil return a table when the concatinated string is passed.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=188742#bm188765

or as Tom suggested insert the values into a table in a database with some values to identify the rows and use that table in the stored procedure.

Regards,
gova