June 17, 2013 at 12:46 pm
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
June 17, 2013 at 12:59 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 1:02 pm
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.
June 17, 2013 at 1:26 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 1:35 pm
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?
June 17, 2013 at 1:49 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:00 pm
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;
June 17, 2013 at 2:28 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:31 pm
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?
June 17, 2013 at 2:35 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:39 pm
Ok Sean, I will do the TVF. But it leads me back to square one sort of, because I can't query the ClearedTransaction stored procedure, the one you gave an example of as "SomeTable". So how would I be able to do that. Also Sean, assuming I get that to work, is it possible to create that function you and I were discussing and putting it into a Stored Procedure? If so, is there any benefit to that?
June 17, 2013 at 2:51 pm
dob111283 (6/17/2013)
Ok Sean, I will do the TVF. But it leads me back to square one sort of, because I can't query the ClearedTransaction stored procedure, the one you gave an example of as "SomeTable". So how would I be able to do that. Also Sean, assuming I get that to work, is it possible to create that function you and I were discussing and putting it into a Stored Procedure? If so, is there any benefit to that?
What is this ClearedTransaction stored proc? Is it really involved? I would think it really nothing more than a select statement? Maybe you could make your iTVF as GetClearedTransactions. Then modify the ClearedTransaction stored proc and have it select from your new function? That way the legacy code will still work and you don't have two copies of the same logic.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:56 pm
I'm sorry Sean, all this time I kept saying ClearedTransactions, I meant the ExchangeRate stored procedure I posted the code in my very first thread. THAT is what I would need to call in the function. There is a ClearedTransactions stored procedure as well as mentioned in the "what I'm supposed to do" but I don't think I'm supposed to be calling 2 different stored procedures inside a new, bigger one. I think you understand the instructions much better than me. But your suggestion sounds interesting, how would I do that? I imagine with ALTER PROC but I'm not sure how to proceed further.
June 17, 2013 at 3:11 pm
dob111283 (6/17/2013)
I'm sorry Sean, all this time I kept saying ClearedTransactions, I meant the ExchangeRate stored procedure I posted the code in my very first thread. THAT is what I would need to call in the function. There is a ClearedTransactions stored procedure as well as mentioned in the "what I'm supposed to do" but I don't think I'm supposed to be calling 2 different stored procedures inside a new, bigger one. I think you understand the instructions much better than me. But your suggestion sounds interesting, how would I do that? I imagine with ALTER PROC but I'm not sure how to proceed further.
Well let's say your current procedure is something like this.
create proc ExchangeRate as
select SomeColumns
from someTable
where SomeCriteria
Now you want to consume inside another proc you could do something like this.
create proc GetExchangeRates(ParamsList)
returns table
return
select SomeColumns
from someTable
where SomeCriteria
Easy enough so far right? All you have done at this point is to create a brand new iTVF that has the exact same logic as your original procedure.
Now of course we don't want two copies of the same logic running around (maintenance and all that other nasty stuff).
So you could just change your original proc to use this new function.
alter proc ExchangeRate as
select SomeColumns from dbo.GetExchangeRates(ParamsList)
Does this make sense? I am really kind of guessing because I have no real idea what your current system, tables and proc look like. 🙂
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 3:15 pm
It does make some sense, it's just a lot of information for a newcomer to absorb. Do you have some time later tonight to maybe help me with this realtime? If so, what is your contact information, whether it's skype or aim?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply