Post reply

Stored Procedure with table parameters

  • April 10, 2009 at 6:52 am

    #130471

    I'm hoping someone can point me in the right direction here. What I'm trying to do is build a procedure which uses values from a subquery of tables within SQL Server as parameters in an openquery statement which pulls data from a linked Oracle database.

    This is a condensed example of the procedure thus far -

    declare @infquery varchar(8000)

    declare @finalquery varchar(8000)

    declare @client varchar(8000)

    declare @code varchar(8000)

    select @client = client from val_list group by client --this only pulls the first record

    select @code = code from val_list --this only pulls the first record

    set @infquery = 'select last_name, first_name, id

    from oracle_table

    where client in (' + '''' + '''' +@client + '''' + '''' + ')

    and code in (' + @code + ')'

    set @finalquery = 'insert into data

    select last_name, first_name, id

    from openquery(oracledb,' + '''' + @infquery + '''' + ')'

    The val_list table would contain multiple rows with different combinations of fields client and code which I want to be used as parameters in the openquery statement. Based on the volume of data I'm working with, and from what I've read, a cursor wouldn't be the best option for this SP, but I haven't been able to locate any other information that points me in the right direction.

    Thanks in advance.

  • April 13, 2009 at 6:16 am

    #976494

    So basically you want to create a comma-delimited list to pass to the IN clause of a query. You can do this using FOR XML PATH. Somthing like this (using AdventureWorks):

    SELECT

    @FirstName = STUFF((select

    ''',''' + FirstName

    from

    person.contact

    where

    'thomas' = lastname AND

    firstname LIKE 'M%'

    FOR

    XML PATH('')), 2, 2, '') + ''''

    YOu can see this thread for an explanation of how/why it works.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 2 (of 2 total)

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