Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6

    Question Unanswered: Multiple Select Statement in one stored procedures

    I have a question.

    I have an issue where I want to grab values from several different tables in a SQL DB. What I would like to know if I can have multiple select statements within one stored procedure, save these values in a temp table,then pass a value to the next select statement.

    After going through all of the select statements and creating the temp tables, then combine the temp tables into one table and pass that table back. To the caller of that stored Procedure?

    The problem is I am trying to reduce duplicates in various joins that I have, and because there are so many tables that I have to touch to gather my data I had to take a step by step approach to achieve what I wanted.

    Here is an example:

    select
    a.FirstName,
    a.LastName,
    b.Address,
    b.City,
    b.State,
    b.Zip,
    c.ModelID,
    c.InvoiceNumber

    from tb_Customer a,
    tb_Address b,
    tb_Order c

    where a.CustomerID = c.CustomerID AND
    b.AddresID = a.AddressID AND
    c.OrderID = a.OrderID

    declare @PassVar as int -- or what ever

    set @PassVar = c.ModelID

    create tb_temp_1 --create a temp table to hold the values of each select statement

    pass @PassVar to the next select statement and save all of the findings into temp tables
    then combine all of the values of that combined temp table back to the application.
    Last edited by MagicCity33; 02-04-09 at 18:10.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This is one of those instances where it looks like you have given a lot of thought to the problem and settled on a solution and are looking how to implement that.

    Take us back three or four steps - what is the business problem, in Natural English (I know you are a Brummie, but have a go )? You know: "This procedure is to support X department who process Y data. The function they need to accomplish here is to receive Z data to inform W decision. The data come from R sources and needs to be in S format". That kinda thing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MagicCity33
    ... then combine the temp tables into one table and pass that table back...
    sounds like a single UNION query would do the job rather than all those ~ptui~ temp tables

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6
    Quote Originally Posted by pootle flump
    Hi

    This is one of those instances where it looks like you have given a lot of thought to the problem and settled on a solution and are looking how to implement that.

    Take us back three or four steps - what is the business problem, in Natural English (I know you are a Brummie, but have a go )? You know: "This procedure is to support X department who process Y data. The function they need to accomplish here is to receive Z data to inform W decision. The data come from R sources and needs to be in S format". That kinda thing.
    This SP would be used to build a bill for a customer listing the customers information, invoicenumber, the product purchased, prices for each item, etc.

    I was just wondering if this was the proper way to try and approach what I want as I am not to sure how to save the results of each of my queries to return one big record back to my application.

    I could have made several calls from VB to grab the information individually but I only wanted to connect to the DB one time then bring everything back.

    There are far more tables that I have to reach out to, than I have listed here this was a simplified version of how the final data will come back. There are over 40 variables that must be passed back, but they require various ID's (parameters) from various tables to populate one record.

    Basically I need to save the results of each query pass an ID variable from that query to the next query to perform the next query, save that information, then continue through the process until I have obtained everything that I need. Finally combine it all in to one record to be passed back.

  5. #5
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6
    Quote Originally Posted by r937
    sounds like a single UNION query would do the job rather than all those ~ptui~ temp tables

    So I could actually set up a number of variables to retain the results of the queries then do a UNION or UNION ALL and combine all of the variables into one record?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no

    my reply about the UNION was based on the information you provided about temp tables

    usually, temp tables consist of more than one column and one row

    i'm now beginning to wonder if perhaps all these tables you need to pull stuff from might be accessed in a join query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6
    Quote Originally Posted by r937
    no

    my reply about the UNION was based on the information you provided about temp tables

    usually, temp tables consist of more than one column and one row

    i'm now beginning to wonder if perhaps all these tables you need to pull stuff from might be accessed in a join query

    Thank you for responding,

    I started off with one big join then found that I was not able to reduce the duplicate values. Each of my select statements have joins in them. The for these select statements have a number of joins range from 2 to 6 tables.

    In the end I need one record with one row and several comlumns of data.

    I was mainly wondering if I could do what I was proposing to do to control the flow and reduce my duplicates as I move along.

    What complicates this is, I have to use part of the result set (variables) as a parameter to perform the next query.

    Breaking it down helps me understand exactly what it is I am trying to accomplish. Again I started off with one statement but moved away from it since I could not understand how to remove the duplicates.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is consuming this data? I think Rudy and I are now convinced your approach is not the right one so there must be some limitation that is causing you to think this is best.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MagicCity33
    ...since I could not understand how to remove the duplicates.
    SELECT DISTINCT ...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6
    Quote Originally Posted by pootle flump
    What is consuming this data? I think Rudy and I are now convinced your approach is not the right one so there must be some limitation that is causing you to think this is best.
    I am consuming this data from a function that I setup in my .Net application. I broke it down like this because I needed to see why I was getting duplicates. Then I got the idea of leaving them seperate and just saving off the results of each of the individual select statements. The issue is I have to use the results of the various statements to populate the next statement. I also do not have control where I could physically remove duplicates from the tables. I inherited an application and just want it to perform the what that it SHOULD. I wanted to develop a plan of attack before changing up anymore.

  11. #11
    Join Date
    Feb 2009
    Location
    B'ham
    Posts
    6
    Thanks for all of your help guys. I went back to my orginal SQL statement and two of my joins where left out. I have a total of 13 joins going so I got confused. I had to walk away and step back into it with a fresh set of eyes. My issue is now resolved.

Posting Permissions

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