Calling Another Stored Procedure

Jeff Moden · April 12th, 2009, 09:04 PM

--===== Identify a "safe" place to "play"
    USE TempDB
GO
--===== Create the test tables
CREATE TABLE dbo.HistoricalLabData
        (
        ID         INT,
        TestDate   DATETIME,
        Department VARCHAR(50),
        Test       VARCHAR(100),
        Result     VARCHAR(20)
        )
CREATE TABLE dbo.LabData
        (
        ID         INT,
        TestDate   DATETIME,
        Department VARCHAR(50),
        Test       VARCHAR(100),
        Result     VARCHAR(20)
        )
--===== Populate the test tables
INSERT INTO HistoricalLabData
        (ID,TestDate,Department,Test,Result)
SELECT '1234','04/01/2009','Chemistry' ,'Glucose','75' UNION ALL
SELECT '1234','01/01/2009','Chemistry' ,'Sodium' ,'130' UNION ALL
SELECT '1234','04/01/2009','Hematology','WBC'    ,'7.50' UNION ALL
SELECT '1234','01/01/2009','Hematology','HGB'    ,'10.0' UNION ALL
SELECT '5678','05/01/2009','Chemistry' ,'Glucose','82' UNION ALL
SELECT '5678','02/01/2009','Chemistry' ,'AST'    ,'25' UNION ALL
SELECT '5678','05/01/2009','Hematology','HCT'    ,'33.0'
INSERT INTO LabData
        (ID,TestDate,Department,Test,Result)
SELECT '1234','01/01/2009','Chemistry' ,'Glucose','75' UNION ALL
SELECT '1234','04/01/2009','Chemistry' ,'Sodium' ,'130' UNION ALL
SELECT '1234','01/01/2009','Hematology','WBC'    ,'7.50' UNION ALL
SELECT '1234','04/01/2009','Hematology','HGB'    ,'10.0' UNION ALL
SELECT '5678','02/01/2009','Chemistry' ,'Glucose','82' UNION ALL
SELECT '5678','05/01/2009','Chemistry' ,'AST'    ,'25' UNION ALL
SELECT '5678','02/01/2009','Hematology','HCT'    ,'33.0'
GO
CREATE VIEW dbo.LatestLabData AS
   WITH cteAllData AS
(--==== Numbers the rows in a way that will make it e asy to pick only the latest rows
SELECT ROW_NUMBER() OVER (PARTITION BY ID,Department,Test ORDER BY TestDate DESC) AS RowNum,
        d.*
   FROM (--==== Combines all of the rows from both tables so we can pick the latest rows
         SELECT *
           FROM dbo.HistoricalLabData
          UNION ALL
         SELECT *
           FROM dbo.LabData
        )d
)--==== Pick only the latest rows
SELECT ID,TestDate,Department,Test,Result
   FROM cteAllData
  WHERE RowNum = 1
GO
--===== Show all the latest rows in order by ID, Dep artment, Test
SELECT *
   FROM dbo.LatestLabData
  ORDER BY ID, Department, Test
--===== Show all the latest rows from the Chemistry department
     -- in order by ID, Department, Test
SELECT *
   FROM dbo.LatestLabData
  WHERE Department = 'Chemistry'
  ORDER BY ID, Department, Test
--===== Show all the latest rows for the Glucose tes t
     -- in order by ID, Department, Test
SELECT *
   FROM dbo.LatestLabData
  WHERE Test = 'Glucose'
  ORDER BY ID, Department, Test
--===== Show all the latest rows for ID 1234
     -- in order by ID, Department, Test
SELECT *
   FROM dbo.LatestLabData
  WHERE ID = 1234
  ORDER BY ID, Department, Test

Jeff Moden · April 12th, 2009, 09:06 PM

Well... that's a little better. I hate forums that are in the business of sharing code and they don't do things like change leading spaces to hard spaces and they don't preserve things like blank lines.

Anyway, we should be all set with the code now,..

eusanpe · April 13th, 2009, 02:17 PM

Jeff,

Thank you for the advice. I will heed it in the future.

As far as what I am needing help with, I am not sure if I explained it well. I will try again.

I have a Calling Program that will execute whatever Stored Proc I instruct it to and pass an 'ID' parameter.

I have a SQL 2000 Database (Historical Database) on a remote server that has a Stored Proc that I can't modify but I have to use. I can't post all of it because of legal crap but I can post a snippet.

Code:

CREATE PROCEDURE [dbo].[HistoricalLabData] 
( 
 @ID varchar(20) 
) 
AS 
create table #HistoricalLabData
( LabID varchar(20), TestDate datetime, Dept varchar(50), Test varchar(100), Result varchar(20) ) 

Declare @LabID varchar(20), 
Declare @LabTestDate datetime, 
Declare @LabDepartment varchar(50), 
Declare @LabTest varchar(100), 
Declare @LabResult varchar(20)

--------------Inserting Values Code------------
<Not displayed>
--------------End Inserting Vaues Code--------

--------------Remove Temporary Table--------
Delete from #HistoricalLabData where TestDate is null 
select * from #HistoricalLabData (nolock) 
 
Go

As you can see, the Remote Stored Proc creates a temporary table called 'HistoricalLabData' and removes it at the end.

I have a Local server running SQL Server 2005. I have to create a Stored Proc on it to query the Current Database for the same data as the Historical Database.

I have to compare the 'TestDates' of the data from the Current DB and the Historical Database using two Stored Procs. Unfortunately I have to use the Stored Proc in the remote database and they want a Stored Proc created locally.

After my Calling Program calls the Local Stored Proc....

can the Local Stored Proc put the results of its query into a View, execute the Remote Stored Proc, get the results from the #HistoricalLabData table that is created and insert into the same View on the Local database, finish executing the Remote Stored Proc so the #HistoricalLabData table is removed?

If I can get the results of both stored procs into a Local View, I can write an ad-hoc query from my Calling Program to get the results that I need. I just need to know how to get the results from the remote table created from the remote stored proc into the Local View, let the remote stored proc finish executing so it will delete the #HistoricalLabData table, get the results from a local stored proc, that will need to be written, into the Local View and how to delete the results in the View or delete the View after my Calling Program gets the results.

I have to use two stored procs and I have control over the local one.

BTW... I really appreciate the help that you and Old Pedant have given me.

Thank you..

Jeff Moden · April 17th, 2009, 10:04 PM

1. Create a temp table similar to the one they did in the remote stored proc but locally.
2. Do an INSERT/EXEC into that temp table using their remote stored proc.
3. Insert similar data from the local table.
4. Use the code similar to what I've already posted to extract the correct data from that tmep table.