Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Querying a stored procedure within a stored procedure Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 12:24 PM
Points: 39, Visits: 110
Hey guys, I'm trying to write a stored procedure and within that stored procedure, I have to call another stored procedure, or rather specifically query it. I have only 6 months experience with sql so I'm learning. Anyways, I know how to query tables while creating a stored procedures but I do not know how to get that within a stored procedure. I need to get certain columns within that stored procedure

Here is what I am supposed to do:


See if you can create a stored procedure based on requirements below:

Title: PEX_MetricsTopFiveStrongest
Summary: This stored procedure will take the passed in LPPProgramID and DateTimePoint parameters and return top 5 program ProgramIDs (uniqueidentifier) based on real time exchange rates. The exchange rate is the ratio of most recent cleared transaction point amount of the LPPProgramID to the other program transaction point amount.

Parameters:
- LPPProgramID (uniqueidentifier) - this will be the the programID against to compare the other programs when comparing the exchange rates - DateTimePoint (DateTime) - this will be the date/time of interest at which to calculate the exchange rate

Returns:
5 programIDs(uniqueidentifier)

See if you can call the "PEX_CalculateExchangeRate" within this stored procedure. If not feel free to reuse the code.



And here is the TSQL of the stored procedure I have to query. Any help would be great, thank you.
USE [PEXDEV]
GO
/****** Object: StoredProcedure [dbo].[PEX_calculateExchangeRate] Script Date: 6/11/2013 8:06:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[PEX_calculateExchangeRate]
@BoughtLoyaltyProgramId UNIQUEIDENTIFIER,
@SoldLoyaltyProgramId UNIQUEIDENTIFIER,
@DateTimePoint DateTime,
@IntervalType varchar(max),
@IntervalValue int
AS
BEGIN

IF (@IntervalType = 'seconds')

BEGIN

Select CAST ([BoughtLoyaltyProgramAmount] AS decimal)/CAST ([SoldLoyaltyProgramAmount] AS decimal) as ExchangeRate, DateTransactionCleared

FROM PEX_ClearedTransactions
WHERE BoughtLoyaltyProgramId = @BoughtLoyaltyProgramId AND
SoldLoyaltyProgramId = @SoldLoyaltyProgramId AND
DateTransactionCleared >= @DateTimePoint AND DateTransactionCleared < DATEADD(ss, @IntervalValue, @DateTimePoint)
ORDER BY DateTransactionCleared DESC

END

ELSE IF (@IntervalType = 'minutes')

BEGIN

Select CAST ([BoughtLoyaltyProgramAmount] AS decimal)/CAST ([SoldLoyaltyProgramAmount] AS decimal) as ExchangeRate, DateTransactionCleared

FROM PEX_ClearedTransactions
WHERE BoughtLoyaltyProgramId = @BoughtLoyaltyProgramId AND
SoldLoyaltyProgramId = @SoldLoyaltyProgramId AND
DateTransactionCleared >= @DateTimePoint AND DateTransactionCleared < DATEADD(mi, @IntervalValue, @DateTimePoint)
ORDER BY DateTransactionCleared DESC
END

ELSE IF (@IntervalType = 'hours')

BEGIN

Select CAST ([BoughtLoyaltyProgramAmount] AS decimal)/CAST ([SoldLoyaltyProgramAmount] AS decimal) as ExchangeRate, DateTransactionCleared

FROM PEX_ClearedTransactions
WHERE BoughtLoyaltyProgramId = @BoughtLoyaltyProgramId AND
SoldLoyaltyProgramId = @SoldLoyaltyProgramId AND
DateTransactionCleared >= @DateTimePoint AND DateTransactionCleared < DATEADD(hh, @IntervalValue, @DateTimePoint)
ORDER BY DateTransactionCleared DESC
END

ELSE IF (@IntervalType = 'days')

BEGIN

Select CAST ([BoughtLoyaltyProgramAmount] AS decimal)/CAST ([SoldLoyaltyProgramAmount] AS decimal) as ExchangeRate, DateTransactionCleared

FROM PEX_ClearedTransactions
WHERE BoughtLoyaltyProgramId = @BoughtLoyaltyProgramId AND
SoldLoyaltyProgramId = @SoldLoyaltyProgramId AND
DateTransactionCleared >= @DateTimePoint AND DateTransactionCleared < DATEADD(d, @IntervalValue, @DateTimePoint)
ORDER BY DateTransactionCleared DESC
END

END
Post #1464330
Posted Monday, June 17, 2013 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
Based on your posted requirements:


Here is what I am supposed to do:


See if you can create a stored procedure based on requirements below:

Title: PEX_MetricsTopFiveStrongest
Summary: This stored procedure will take the passed in LPPProgramID and DateTimePoint parameters and return top 5 program ProgramIDs (uniqueidentifier) based on real time exchange rates. The exchange rate is the ratio of most recent cleared transaction point amount of the LPPProgramID to the other program transaction point amount.

Parameters:
- LPPProgramID (uniqueidentifier) - this will be the the programID against to compare the other programs when comparing the exchange rates - DateTimePoint (DateTime) - this will be the date/time of interest at which to calculate the exchange rate

Returns:
5 programIDs(uniqueidentifier)


I would look into making this an iTVF instead of a stored proc. That way you can join to it like any other table.

Something like this:

create function PEX_MetricsTopFiveStrongest
(
@LPPProgramID int,
@DateTimePoint datetime
)
returns table as
return
select top 5 [Columns]
from SomeTable
where LPPProgramID = @LPPProgramID
and DateTimePoint = @DateTimePoint



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464333
Posted Monday, June 17, 2013 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 12:24 PM
Points: 39, Visits: 110
Wow Sean thank you so much for that quick response. I understand what you wrote except, "return Sometable". Since I'm querying a stored procedure dbo.PEX_ClearedTransactions, how would I call that? I guess you're write, a TVF might be easier but I'm supposed to do a SP.
Post #1464335
Posted Monday, June 17, 2013 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
dob111283 (6/17/2013)
Wow Sean thank you so much for that quick response. I understand what you wrote except, "return Sometable". Since I'm querying a stored procedure dbo.PEX_ClearedTransactions, how would I call that?


You have me at a disadvantage here. It seems you have some code that is already in use and some other that you are writing. The problem from my end is that I have no idea what is already in use, what the tables are, what procs you have in place etc. The reason I said SomeTable is because I have no idea where that data would come from.

If you already have a proc that you want to use you need to capture those details into a table.

I can give you an example of how you can capture the results of a stored proc into a temp table. Keep in mind that this assumes your proc ONLY returns a single result set. If it returns multiple result sets this becomes a bit more complicated.

First we need a stored proc.

create proc CaptureExample
as
select top 5 name, object_id, type, type_desc from sys.objects

This can represent your existing proc. Ignore that there is a top without an order by, this may very well return different results each time you run it (this is a topic for another day).

Now you want to capture those results into a table (a temp table in my example).

create table #MyCapture
(
name sysname,
object_id int,
type char(2),
type_desc nvarchar(60)
)

insert #MyCapture
exec CaptureExample

select * from #MyCapture

Does that help?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464339
Posted Monday, June 17, 2013 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 12:24 PM
Points: 39, Visits: 110
I'm sorry Shawn, as a relative newbie to SQL, I'm not properly clarifying things. When you wrote sometable, I realize that's whatever table I'm trying to pry the information from into the current stored procedure or function that I'm working on. That "sometable" is the dbo.PEX_ClearedTransactions stored procedure I'm trying to get all the relative information from, into the new stored procedure/function i'm trying to create, which is the PEX_MetricsTopFiveStrongest. Also Shawn, why do you use Top 5? Can I also do an ORDER BY and use OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY?
Post #1464341
Posted Monday, June 17, 2013 1:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
dob111283 (6/17/2013)
I'm sorry Shawn, as a relative newbie to SQL, I'm not properly clarifying things. When you wrote sometable, I realize that's whatever table I'm trying to pry the information from into the current stored procedure or function that I'm working on. That "sometable" is the dbo.PEX_ClearedTransactions stored procedure I'm trying to get all the relative information from, into the new stored procedure/function i'm trying to create, which is the PEX_MetricsTopFiveStrongest. Also Shawn, why do you use Top 5? Can I also do an ORDER BY and use OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY?


I used top 5 because your requirements said to retrieve the top 5.

Look at the recent example I posted. There is no particular reason for top 5. I could just as easily made that example proc get all items from sys.objects but that is a lot of information to retrieve for a simple example. Since you are in 2012 you could use OFFSET but it would do the same thing as top 5. Regardless of the method you use to get the "top 5" you MUST specify an order.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464350
Posted Monday, June 17, 2013 2:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 12:24 PM
Points: 39, Visits: 110
Ok Sean I appreciate the help but bear with me as I take this one step at a time. So per your instructions, would the first Stored Procedure be the following?

CREATE PROC PEX_MetricsTopFiveStrongest
AS

SELECT TransactionId AS LPPProgramID, DateTransactionCleared AS DateTimePoint
FROM dbo.Pex_ClearedTransactions
ORDER BY DateTimePoint
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Post #1464360
Posted Monday, June 17, 2013 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
dob111283 (6/17/2013)
Ok Sean I appreciate the help but bear with me as I take this one step at a time. So per your instructions, would the first Stored Procedure be the following?

CREATE PROC PEX_MetricsTopFiveStrongest
AS

SELECT TransactionId AS LPPProgramID, DateTransactionCleared AS DateTimePoint
FROM dbo.Pex_ClearedTransactions
ORDER BY DateTimePoint
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;


No, you can't select from a stored proc. You would have to do something like in the example procedure I posted. You would have to insert the resultset of Pex_ClearedTransactions into a temp table and then select from the temp table. This might be horribly inefficient if that procedure returns a lot of data.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464372
Posted Monday, June 17, 2013 2:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 12:24 PM
Points: 39, Visits: 110
Crap, seriously? So everytime I wanted to get realtime results from the transactions, I'd have to manually query the ClearedTransactions table, insert results into a temp table, and then do whatever it is you said to do next? I've read about nesting, that is stored procedures within stored procedures but I'm not sure how to do any of that. So are you basically saying there's no real way to create a stored procedure that queries the results of another stored procedure?
Post #1464373
Posted Monday, June 17, 2013 2:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
dob111283 (6/17/2013)
Crap, seriously? So everytime I wanted to get realtime results from the transactions, I'd have to manually query the ClearedTransactions table, insert results into a temp table, and then do whatever it is you said to do next? I've read about nesting, that is stored procedures within stored procedures but I'm not sure how to do any of that. So are you basically saying there's no real way to create a stored procedure that queries the results of another stored procedure?


Correct. This is why I suggested using iTVF. It is designed for doing this type of thing. Assuming you keep the body of the function to a single statement it is also super fast!!!


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464374
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse