Post reply

pass a table as a sproc parameter

  • October 31, 2003 at 4:18 am

    #162671

    Hi folks,

    I would like to pass a table into my sproc as a parameter.No joy, I know that I can use UDF's that return a table, but is there any way that I can achieve the underneath?

    eg.

    CREATE PROCEDURE [dbo].[spSelectFromLookup]

    (

    @lookupTable TABLE

    )

    AS

    SELECT *

    FROM @lookupTable

    GO

    when I try to save this sproc, I get an error:

    "Must declare the variable, @lookupTable"

    Can anyone help?

    cheers,

    yogiberr

  • October 31, 2003 at 4:39 am

    #480421

    You can create and populate a temp table then access it from within the SP.

    Cursors never.

    DTS - only when needed and never to control.


    Cursors never.
    DTS - only when needed and never to control.

  • November 3, 2003 at 2:11 am

    #480600

    try using dynamic sql in the SP e.g.

    EXEC ("Select * from " + @msLookUptable)

  • November 3, 2003 at 2:49 am

    #480602

    Nice one,

    thanks for the replies folks.

    yogi

  • November 3, 2003 at 1:04 pm

    #480693

    I like this, What's the final product (SQL Statement) look like?

    Isaiah


    -Isaiah

  • November 6, 2003 at 2:48 am

    #481018

    Hi, sorry for late reply.

    i eneded up buiding the sql as text, as opposed to a stored proc.

    So, I did something like:

    dim sSql as string

    sSql ="SELECT * FROM " &tableName

    and i then passed this variable to the database as commandtext.maybe not the best way, but that's what i did.

    hth

Viewing 6 posts - 1 through 5 (of 5 total)

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