Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Combing multiple returned tables in a stored procedure

Posted on 2007-07-25
10
Medium Priority
?
228 Views
Last Modified: 2010-08-05
I an attempt to make my stored procedures more simple (modular) I've decided to split one of my stored procedures that Selects from mutiple tables into 2 stored procedures with 1 stored procedure calling the other.

Somethinng like below(GetTravelPolicy call GetPolicy and they both return data):

ALTER PROCEDURE [dbo].[GetTravelPolicy]
   (@PolicyGUID uniqueidentifier)
AS

EXEC GetPolicy @PolicyGUID

SELECT        *
FROM            TravelDetails
WHERE        (PolicyGUID = @PolicyGUID)

How do I make the stored procedure act as if its returning a single table?
0
Comment
Question by:g-spot
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19564337
please give the structure of the sp and the table structure
0
 

Author Comment

by:g-spot
ID: 19564380
Hi aneeshattingal. These are simplified versions of the 2 SPs. I want the first stored procdure to return data from both SPs as 1 table.

Stored Procedure 1:

ALTER PROCEDURE [dbo].[GetTravelPolicy]
   (@PolicyGUID uniqueidentifier)
AS

EXEC GetPolicy @PolicyGUID

SELECT        *
FROM            TravelDetails
WHERE        (PolicyGUID = @PolicyGUID)

Stored Procedure 2:

ALTER PROCEDURE dbo.GetPolicy
   (@PolicyGUID uniqueidentifier)
AS
SELECT        PolicyID, PolicyGUID, EntryDate, QuoteID, CustomerID, TransactionID, PaymentMethod, Processed, ProcessedDate, CertificateNumber, ProviderCode
FROM            Policies
WHERE        (PolicyGUID = @PolicyGUID)

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19564460
Better you avoid multiple sp calls and combine as 1

ALTER PROCEDURE [dbo].[GetTravelPolicy]
   (@PolicyGUID uniqueidentifier)
AS

SELECT        PolicyID, PolicyGUID, EntryDate, QuoteID, CustomerID, TransactionID, PaymentMethod, Processed, ProcessedDate, CertificateNumber, ProviderCode
FROM            Policies
WHERE        (PolicyGUID = @PolicyGUID)

UNION
SELECT        * ---  Replace * and place the exact no of coulmns in the order we srote the above query
FROM            TravelDetails
WHERE        (PolicyGUID = @PolicyGUID)
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:g-spot
ID: 19564614
Hi aneeshattingal

The reason why I wanted to go with this "layered" method was that I was planning to have 4 or 5 SPs that would each call the GetPolicy SP

e.g.
GetWeddingPolicy calls GetPolicy
GetTravelPolicy calls GetPolicy
GetPetPolicy call GetPolicy

But if its not  a good idea I ill go with your suggestion.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 19564695
i understood that, but if you have an option to avoid it, please avoid Nesting as it is difficult for error handling
Now, if you still wanto go ahead with nested proc calls , make sure that
1. all the sps have "SET NOCOUNT ON" statement immediately after that sp
2. after each sp call , do an error validation  something like  
     exec  somesp
     IF @@Error <> 0
     Begin
        Print 'Error happened"
        --- and do whatever u need to do
     End

3. Since the sp returns a record set, in order to get the values in the parent sp, you need to declare a temperory table with EXACTLY the SAME NO.OF COLUMNS and DATAType and u can store the values of exec sp inside this table

   CREATE TABLE #Temptable ( .......................)
    INSERT INTO #TempTable
    EXEC GetPolicy @PolicyGUID


4. I have been asking for the table structure of "TravelDetails" , without which i can't give you the exact solution
    If both #TempTabe and TravelDetails have the same structure then this will return you the desired results
   
   SELECT * FROm #TempTable
UNION
SELECT        * ---  Replace * and place the exact no of coulmns in the order we srote the above query
FROM            TravelDetails
WHERE        (PolicyGUID = @PolicyGUID)


   

 
 
0
 

Author Comment

by:g-spot
ID: 19564795
Am I right in saying that the UNION wont work if the second part of the query does not have the same number of columns as the first part?

I know that the second part will have many more columns than the first part.
0
 

Author Comment

by:g-spot
ID: 19564803
aneeshattingal:

#TempTable and TravelDetails will have different structures.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19564822
it will work, but u need to modify your query slightly
say the sp returrns 2 columns Id and Name and the temp tablle contains both. and the second select part returns id,  age, adress.. so in order to return these to in a union statement

SELECT ID, Name, NULL as age, Null as Address
from #Temp
UNION ALl
SELECT ID, Null, age, address
from secondtable
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question