Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Unanswered: Multiple Selects in a Stored Proc

    I need to perform 2 selects in a stored procedure, but only return data from the second..

    eg.

    first select gets the proper table to search from a transaction summary table

    select category from trans summary where transsummary.transid = @transid

    based on the category returned something like this

    if category = 1
    then tabletosearch = table1
    end if
    if category = 2
    then tabletoseach = table 2
    endif

    select * from @tabletosearch


    Is this possible, or am I going about this the wrong way?...
    As you probably can tell I am new at this.
    Thanks folks
    Last edited by GregCrossan; 11-05-02 at 18:07.

  2. #2
    Join Date
    Nov 2002
    Posts
    23
    Try something like...

    select @category = (select category from trans summary where transsummary.transid = @transid)

    if @category = 1
    then @tabletosearch = table1
    end if
    if @category = 2
    then @tabletoseach = table 2
    endif

    select * from @tabletosearch

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Code:
     
    DECLARE @category        int,
            @tabletosearch sysname
    
    SELECT @category = t.category 
    FROM  transsummary t
    WHERE t.transid = @transid
    
    if (@category = 1) 
        SET @tabletosearch = 'table1'
    
    if (@category = 2) 
        SET @tabletosearch = 'table2'
    
    
    EXEC ('SELECT * FROM ' + @tabletosearch)
    MCDBA

  4. #4
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    That worked great if I used SELECT * but as soon as I try to use only specific fields......

    Originally posted by achorozy
    Code:
     
    DECLARE @category        int,
            @tabletosearch sysname
    
    SELECT @category = t.category 
    FROM  transsummary t
    WHERE t.transid = @transid
    
    if (@category = 1) 
        SET @tabletosearch = 'table1'
    
    if (@category = 2) 
        SET @tabletosearch = 'table2'
    
    
    EXEC ('SELECT * FROM ' + @tabletosearch)

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

    Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

    So why not just do this:
    Code:
    DECLARE @category        int
    
    SELECT @category = t.category 
    FROM  transsummary t
    WHERE t.transid = @transid
    
    if (@category = 1) 
        SELECT col1, col2, col3 FROM table1
    
    if (@category = 2) 
        SELECT colA, colB, colC FROM table2
    Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
    What doesn't work? What error did you get? Why doesn't it work?
    MCDBA

  6. #6
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

    So far I am getting to about 7/8ths of the way thru want I need to do.
    SO I will do, what I should have done the first time. give all the details

    Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

    I need to be able to get data from 3 tables and only certain fields from each table

    so

    select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

    S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

    and obviously the the corresonding Sales Rep Detail & Office Detail....

    Thanks so much for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •