SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Querying a stored procedure within a stored procedure


Querying a stored procedure within a stored procedure

Author
Message
dob111283
dob111283
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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 Modens 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)
dob111283
dob111283
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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 Modens 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)
dob111283
dob111283
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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. :-P

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. :-D 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 Modens 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)
dob111283
dob111283
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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;
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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 Modens 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)
dob111283
dob111283
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search