Post reply

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

  • 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?

  • June 15, 2005 at 5:09 am

    #565620

    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)

  • June 15, 2005 at 5:32 am

    #565626

    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 🙂

  • June 15, 2005 at 6:41 am

    #565644

    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://qa.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

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

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