Calling Another Stored Procedure

eusanpe · April 6th, 2009, 04:56 PM

Hello All,

I am trying to figure out what would be the best way to retrieve the latest data.

Code:

Historical Database:
ID #   | Test Date    | Department   | Test       | Result
=============================================================
1234     01/01/2009     Chemistry      Glucose       75
1234     01/01/2009     Chemistry      Sodium      130
1234     01/01/2009     Hematology    WBC          7.50
1234     01/01/2009     Hematology    HGB          10.0
5678     03/01/2009     Chemistry      Glucose       82     
5678     03/01/2009     Chemistry      AST             25
5678     03/01/2009     Hematology    HCT           33.0

Current Database:
ID #   | Test Date    | Department   | Test       | Result
=============================================================
1234     04/01/2009     Chemistry      Glucose       75
1234     04/01/2009     Chemistry      Sodium      130
1234     04/01/2009     Hematology    WBC          7.50
1234     04/01/2009     Hematology    HGB          10.0
5678     02/01/2009     Chemistry      Glucose       82     
5678     02/01/2009     Chemistry      AST             25
5678     02/01/2009     Hematology    HCT           33.0

There is a stored procedure on the historical database that will return each row in a dataset.
What I need to do is compare the 'Test Date' between the two databases and retrieve the data from the newest date. Can I use a stored procedure in the 'Current Database' to call the stored procedure from the 'Historical Database', compare the dates between the two for each 'ID #' and return the newest data?

Thank you.

Old Pedant · April 7th, 2009, 12:59 AM

Shouldn't be very hard, at all.

You didn't say, but I *assume* you want to do this on a PER ID, PER DEPARTMENT, PER TEST basis.

That is, if you have three different TEST values for a given ID, you want a record for each TEST. Ditto departments.

If my assumption is wrong, explain more what you are after.

Code:

CREATE VIEW combined
AS 
    SELECT 'M' as tablename, id, testdate, department, test, result FROM dbo.table
    UNION
    SELECT 'H', id, testdate, department, test, result FROM historical.dbo.table

SELECT C.*
FROM ( SELECT MAX(testdate) AS mindate, id, department, test
           FROM combined
           GROUP BY id, department, test ) AS M,
         combined AS C
WHERE M.mindate = C.testdate
  AND M.id = C.id
  AND M.department = C.department
  AND M.test = C.test
ORDER BY C.id

First of all, SQL Server allows you to refer to other databases by just using the 3 part form shown there (change dbo to whatever you use if it's not dbo). [You can even use a 4 part form that includes the name of a different database server, provided the other server is accessible to you, using OPENDATASOURCE. But we won't go into that.]

So we simply create a UNION of all the records from both tables, so that we can treat the result as a logical single table.

Then we use the inner SELECT in our main query to find the latest date in the combined table, by ID, department, and test. [Notice that we disregard the table name which was supplied in the UNION and the result.]

Finally, we JOIN that list of latest date records back to the unioned view to get the table identifier ('M' for main table, 'H' for historical...didn't know if you cared which table data is from, but gave it to you just in case) and the RESULT for that corresponding record.

ONLY DIFFICULTY: If you have two records (either in same table or one in each table) with the same date, id, department, test, then you will get multiple records in the output. If that's a problem, there's a way around that, too.

eusanpe · April 7th, 2009, 02:24 AM

Thank you for the reply. That was very helpful.
On the historical db, there is already a stored procedure written that will return all the records for a given ID#. So in the example, ID# 1234 has four records associated with it. The stored procedure on the historical db will return each record individually in a dataset. I am not worried about duplicate records because there will not be a two tests with the same test date for a given ID#. I forgot that there is a test time field also.

So I was thinking that a stored procedure would be more efficient on the main db. The main db stored procedure would call the historical db's stored procedure, get the data from it, compare the data against the data from the main database stored procedure and print out the all the newest data for the given ID# based on which records have the newest date.

I hope that makes sense.

Thank you again.

Old Pedant · April 7th, 2009, 03:10 AM

Yes, I just assumed you would take my SELECT there--the one that follows the VIEW--and turn it into a stored proc.

Not clear to me why you need a SP on the historical db. What does it do that the simple SELECT I use there in the UNION does not do???

We do not *WANT* "all the records for a given ID#". We want all records for all ID numbers from the historical table. Or at least that's how you presented the problem.

If the SP in the main DB had to call the SP in the history DB once per ID#, presumably by using a cursor on the records in the main DB, it would be HORRIBLY slow! Just ugly ugly ugly.

You keep mentioning a "data set", but I don't understand where the comes in. Yes, you could use the result of my final SELECT there to populate a data table in a data set in the .NET framework. But you could also just use a datareader or you could even be using ASP or maybe even a ".vbs" program to read the records.

The SQL query should do *ALL* the work in one go. You should not be hitting the DB more than one time.

Curiosity: Who split the date and time into two separate fields, as you seem to be saying?? Shame on him/her. Much more efficient to leave both in a single field.

For instance, in this case we will have to add the date and time back together to create a single datetime value to use in the MAX( ) expression. Ugh.

So:

Code:

CREATE VIEW combined
AS 
    SELECT 'M' as tablename, id, testdate+testtime AS tdatetime, department, test, result FROM dbo.table
    UNION
    SELECT 'H', id, testdate+testtime, department, test, result FROM historical.dbo.table

GO

CREATE PROCEDURE getLatestTestResults
AS
SELECT C.*
FROM ( SELECT MAX(tdatetime) AS maxdate, id, department, test
           FROM combined
           GROUP BY id, department, test ) AS M,
         combined AS C
WHERE M.maxdate = C.tdatetime
  AND M.id = C.id
  AND M.department = C.department
  AND M.test = C.test
ORDER BY C.id

GO

I *think* you can simply add a date-only and a time-only value with the plus operator, as I show in the UNION. If not, we'd have to use DATEADD and DATEDIFF to do it.

Code:

SELECT DATEADD(s, testdate, DATEDIFF(s,'0:00:00',testtime) ) AS tdatetime, ... etc ...

See what a pain it is to have the date and time in separate DB fields???

eusanpe · April 7th, 2009, 10:57 AM

I am truly sorry. I mistyped the idea that there was a separate time field. It is combined with the test date.

As far as the stored procedure on the historical db, it was already there. It just performs some testing to validate the data. I don't think that it is needed. One stored proc on the main DB should be enough.

As far as the records in the historical db, I only want to use the records for the one given ID# if the 'test date' is newer than the 'test date' in the main db. If there is no data in the main db, then I will use the data in the historical db. If there is data in both db's, then the main db should have the newest data. If both db's don't have a record for a given ID#, then the new data will go into the main db and the new ID# will be created.

Also, I want all of the records for one ID# that I specify. If I specify ID# 1234, then I only want all of the records for ID# 1234. Basically it will populate some fields on a form if I do a lookup of ID# 1234 with the newest data.

As I said, I think your solution is alot cleaner.

Thank you

Old Pedant · April 7th, 2009, 03:10 PM

Well, the whole point of using a UNION as I showed it was to get ALL records from BOTH dbs that match. I added in that 'H' or 'M' field to tell you which DB the latest record is from, so you can use that in your ASP.NET (I assume) logic if you need it.

As for limiting to a single ID: Sure, but that's not what you said in first post. But no biggy. Just add it to the SP:

Code:

CREATE PROCEDURE getLatestTestResultsForId
    @id INT 
AS
SELECT C.*
FROM ( SELECT MAX(tdatetime) AS maxdate, department, test
           FROM combined
           WHERE id = @id
           GROUP BY department, test ) AS M,
         combined AS C
WHERE M.maxdate = C.tdatetime
  AND M.id = C.id
  AND M.department = C.department
  AND M.test = C.test
ORDER BY C.department, C.test

That will only get records for the single ID that you pass to the SP.

Keep the other SP around in case you ever need results for all IDs, I guess.

***************

EDIT: Just noticed that I inadvertently removed the ID field from the inner SELECT. It must be there in order to do the JOIN back to the C instance of Combined.

so:

Code:

...
SELECT C.*
FROM ( SELECT MAX(tdatetime) AS maxdate, id, department, test
...

eusanpe · April 7th, 2009, 03:17 PM

I reread my first post and you were right. It sounded like I needed all records. My apologies.

Thank you for all of your help. I really appreciate it.

eusanpe · April 12th, 2009, 02:19 AM

I have another question..

I have been informed that I have to use the Stored Proc on the historical DB and that it occasionally is still used. This Stored Proc has some logic for null values and such. Below is a small piece of the code:

Code:

create proc [dbo].[HistoricalLabData] 
( @LabID varchar(20)  )
  as
create table #LabData
(  LabID varchar(20), TestDate datetime, Dept varchar(50), Test varchar(100), Result varchar(20) )

Some variables are declared and inserted into the #LabData temp table. Example of data is below.

What would be the best way to utilize this. I will be passing a variable from another program called 'PatLabNo' to @LabID on the historical stored proc and to @ID on the stored proc that you helped me with.

I have been reading that parameters can't be used to create a view..is that correct?

Once I pass the 'PatLabNo', I need to evaluate the most current test date for each lab test between the main DB and the historical DB for the passed 'PatLabID'. It looks like that one test could be newer in the main DB and another test could be newer in the historical DB. Why the heck are they calling it 'Historical'???

Is there a way to use the #LabData table from the historical DB, create a temp table #MainLabData in the main DB's stored proc and put the values of both tables into a view and then work the magic to get the most current date...or is there an easier solution?

Code:

Historical Database:
ID #   | Test Date    | Department   | Test       | Result
=============================================================
1234     04/01/2009     Chemistry      Glucose       75
1234     01/01/2009     Chemistry      Sodium      130
1234     04/01/2009     Hematology    WBC          7.50
1234     01/01/2009     Hematology    HGB          10.0
5678     05/01/2009     Chemistry      Glucose       82     
5678     02/01/2009     Chemistry      AST             25
5678     05/01/2009     Hematology    HCT           33.0

Current Database:
ID #   | Test Date    | Department   | Test       | Result
=============================================================
1234     01/01/2009     Chemistry      Glucose       75
1234     04/01/2009     Chemistry      Sodium      130
1234     01/01/2009     Hematology    WBC          7.50
1234     04/01/2009     Hematology    HGB          10.0
5678     02/01/2009     Chemistry      Glucose       82     
5678     05/01/2009     Chemistry      AST             25
5678     02/01/2009     Hematology    HCT           33.0

Thank you.

Jeff Moden · April 12th, 2009, 08:46 PM

Heh... Are we using SQL Server 2005 or not? Let's use some of it's features to really simplify this problem...

First of all, eusanpe... it looks like you might be spending a fair amount of time formatting the data you're presenting on this post. Why not do it in such a fashion that makes it easy on folks to actually test their code before posting it? You need to post the code to create the example tables and then post the readily consumable code to populate them. Like this...

Code:

--===== Identify a "safe" place to "play"
USE TempDB
GO
--===== Create the test tables
CREATETABLE dbo.HistoricalLabData
        (
        ID         INT,
        TestDate   DATETIME,
        Department VARCHAR(50),
        Test       VARCHAR(100),
        Result     VARCHAR(20)
)
CREATETABLE dbo.LabData
        (
        ID         INT,
        TestDate   DATETIME,
        Department VARCHAR(50),
        Test       VARCHAR(100),
        Result     VARCHAR(20)
)
--===== Populate the test tables
INSERTINTO 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'
INSERTINTO 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'

Once you've done that for folks, the answers will come quicker and be better. For example, look how simple it is to create a view to return only the latest dated rows for each ID, Department, and Test...

Code:

CREATEVIEW dbo.LatestLabData AS
WITH cteAllData AS
(--==== Numbers the rows in a way that will make it easy to pick only the latest rows
SELECTROW_NUMBER()OVER(PARTITIONBY ID,Department,Test ORDERBY 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

From there, everything becomes as simple as selecting from a table whether by script or stored procedure...

Code:

--===== Show all the latest rows in order by ID, Department, Test
SELECT*FROM dbo.LatestLabData
  ORDERBY 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'
ORDERBY ID, Department, Test
--===== Show all the latest rows for the Glucose test
-- in order by ID, Department, Test
SELECT*FROM dbo.LatestLabData
  WHERE Test ='Glucose'
ORDERBY ID, Department, Test
--===== Show all the latest rows for ID 1234
-- in order by ID, Department, Test
SELECT*FROM dbo.LatestLabData
  WHERE ID = 1234
  ORDERBY ID, Department, Test

Of course, because of the ORDER BY's, I'd recommend you put an index on the underlying tables that include ID, Department, and Test.

Jeff Moden · April 12th, 2009, 08:59 PM

Wow... the code windows on this forum suck pretty badly... it changed a bunch of stuff and deleted a whole lot of spaces. Gimme a minute... I'm trying to fix it all.