Use output from Stored Procedure ms sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use output from Stored Procedure ms sql 2005

Author

Topic

cmowbray
Starting Member

5 Posts

Posted - 2010-01-14 : 06:56:46

I am trying to use the outputs LatestDate and StartDate from a stored procedure called dbo.Projects_CFF_Test as below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[Projects_CFF_TEST]
AS
SELECT Project_ID,

CASE WHEN SMT_Decision_Date >= DFP_Approval_Date And SMT_Decision_Date>= Ministerial_Approval_Date THEN SMT_Decision_Date
WHEN DFP_Approval_Date Is Null AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN SMT_Decision_Date >= DFP_Approval_Date AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date Is Null Then DFP_Approval_Date
WHEN Ministerial_Approval_Date >= SMT_Decision_Date And DFP_Approval_Date Is Null Then Ministerial_Approval_Date
WHEN SMT_Decision_Date>= Ministerial_Approval_Date And DFP_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND DFP_Approval_Date >= Ministerial_Approval_Date THEN DFP_Approval_Date
WHEN Ministerial_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date >= DFP_Approval_Date THEN Ministerial_Approval_Date
ELSE NULL

END AS 'LatestDate'

FROM dbo.v_ApprovedProjCFFView

SELECT Project_ID,
CASE WHEN Max_Actual_Start_Date Is Not Null Then Max_Actual_Start_Date
WHEN Max_Est_Start_Date Is Not Null And Max_Actual_Start_Date Is Null Then Max_Est_Start_Date
Else NULL

END AS 'StartDate'

FROM dbo.v_ApprovedProjCFFView

I need to use the LatestDate and StartDate from the above stored procedure to filter results so that only result set where StartDate < LatestDate is obtained so that I can create a view in a web application to display the results. I will be most grateful if you can point me in the right direction as I am new to ms sql and am at a loss. Many Thanks in advance for any assistance.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 07:27:25

You can insert results into a table (either physical table, or a #TEMP temporary table) using:


INSERT INTO #MyTempTable
EXEC MyStoredProcedure

but that only works, AFAIK, if the SProc returns only one resultset. Your SProc returns two

SProcs can have Parameters, so you could pass some additional parameters such that the Sproc filtered the results for you.

You could also have Parameters that OUTPUT (Return) values. So you could call the SProc AND get back specific values for LatestDate and StartDate. You could have a parameter which said whether the SProc should also output the two resultsets, or not.

You could also combine the two resultsets into one, and then you could store that into a temp table (although I'd doubtful that that would be a good route).

Lastly, you could have a User Defined Function, instead of a stored procedure, that returned the values. You could then use the result(s) from that directly in other queries.

cmowbray
Starting Member

5 Posts

Posted - 2010-01-14 : 08:15:09

Thanks Kirsten for the pointers.
I have been looking at all these options but still can't see which is the best one to use. The User Defined function route might be the best but no sure how to apply it.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 08:31:42

If you want more than one "object" returned from a UDF then the UDF has to return a table. So your code would need to be something like:


UPDATE Y
SET ...
FROM dbo.MyUDF(... OptionalParameters ...) AS X
     JOIN SomeTable AS Y
          ON Y.MyDate >= X.StartDate
         AND Y.MyDate < X.LatestDate

cmowbray
Starting Member

5 Posts

Posted - 2010-01-14 : 08:44:31

Thanks Kristen

I will have a look at this.

P.S sorry for spelling your name wrong is last post

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 08:58:49

Don't waste any time worrying about things like that!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 09:44:47

I also thing it might be best going for udf method as you will have flexibility of calling the udf for each row of table and also passing any input values from table rows to perform some action using apply operator

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources