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 2:39 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 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?
Post #1464377
Posted Monday, June 17, 2013 2:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
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 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 #1464385
Posted Monday, June 17, 2013 2:56 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 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.
Post #1464388
Posted Monday, June 17, 2013 3:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
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 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 #1464396
Posted Monday, June 17, 2013 3:15 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
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?
Post #1464400
Posted Monday, June 17, 2013 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
dob111283 (6/17/2013)
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?


Take it one step at a time. First see if you can create a new version of the original proc as an iTVF. This will solve the immediate need. Then look at making it nice and tidy.


_______________________________________________________________

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 #1464405
Posted Monday, June 17, 2013 3:24 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 thanks. I'll post back as soon as I do that. You've been a big help. I really enjoy doing this SQL stuff. It's almost like a chess match.
Post #1464408
Posted Wednesday, June 19, 2013 4:13 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 Sean, I went back and redid the whole thing. I changed the ExchangeRate function and and added an "exchangerate" column in the clearedtransacions table to calculate the exchange rate, making this metrics procedure much easier.


CREATE PROC dbo.PEXDEV_MetricsTopFiveWeakest

@LPPProgramID UNIQUEIDENTIFIER,
@DateFrom DATETIME,
@DateTo DATETIME

AS

SELECT TOP(5) BoughtLoyaltyProgramId, SoldLoyaltyProgramId, DateTransactionCleared, ExchangeRate
FROM dbo.PEX_ClearedTransactions
WHERE DateTransactionCleared>=@DateFrom AND DateTransactionCleared<@DateTo
AND (BoughtLoyaltyProgramId=@LPPProgramID
OR SoldLoyaltyProgramId=@LPPProgramID)
ORDER BY ExchangeRate ;

And the strongest stored procedure would just order by ExchangeRate DESC;

My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.

Post #1465422
Posted Monday, June 24, 2013 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
dob111283 (6/19/2013)

My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.



When you say it won't let you what does that mean? Do you get an error message? What is the actual code? I still don't really understand why you want to use offset fetch when you are doing the same thing as top since the offset is 0.


_______________________________________________________________

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 #1466733
Posted Monday, June 24, 2013 7:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
dob111283 (6/19/2013)
Hey Sean, I went back and redid the whole thing. I changed the ExchangeRate function and and added an "exchangerate" column in the clearedtransacions table to calculate the exchange rate, making this metrics procedure much easier.


CREATE PROC dbo.PEXDEV_MetricsTopFiveWeakest

@LPPProgramID UNIQUEIDENTIFIER,
@DateFrom DATETIME,
@DateTo DATETIME

AS

SELECT TOP(5) BoughtLoyaltyProgramId, SoldLoyaltyProgramId, DateTransactionCleared, ExchangeRate
FROM dbo.PEX_ClearedTransactions
WHERE DateTransactionCleared>=@DateFrom AND DateTransactionCleared<@DateTo
AND (BoughtLoyaltyProgramId=@LPPProgramID
OR SoldLoyaltyProgramId=@LPPProgramID)
ORDER BY ExchangeRate ;

And the strongest stored procedure would just order by ExchangeRate DESC;

My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.



You are making things incredibly difficult for people who want to help you by not providing sample data. The fact that English seems not to be your native language only serves to muddy the already murky waters.

Can you provide sample base data - for querying - and sample output showing what you would want to see, based on the sample input? It will save everyone's time in the long run.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1466739
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse