Dynamic Criteria for a Stored Procedure

Our database utilizes some custom functions and .dll's to perform IRR
calcuations. The IRR calculation takes two steps.
1. The first step is to create a view which preps the cash flows and
pulls out what you want to analyze. ("Ad Hoc Query")
2. The second step is to create a stored procedure that references the
Ad Hoc Query and performs the IRR calculation ("IRR sp"). The IRR sp
has three important parameters:
a. a start date. If the start date is null, then spGetPerformance
performs the calculation from the earliest date.
b. an end date. This is typically set to the most recent quarter
end date.
c. @.ShowTransactions. This allows us to audit the constituents of
the calculation. 0 = return a single record that is the IRR. 1 =
return all the constituents such that a user could transfer the
recordset to excel and perform the IRR calculation to double check that
spGetPerformance is performing well and there are no errors in data
entry for the constituents.
A sample of the IRR sp is written below:
ALTER PROCEDURE sp10_IRRSinceInceptionByInvestor
AS
BEGIN SET NOCOUNT ON
exec spGetPerformance @.AdHocQueryName =
'sp10_view_IRRSinceInceptionByInvestor',
@.StartDate = NULL, @.EndDate =
'6/30/2005', @.ShowTransactions = 0
END
---
Now I have about 30 of these IRR sp and they all have EndDates that are
the same or somehow systematically related (e.g. 5 are set one year
earlier, 5 are set 3 years earlier, etc.).
Is there a way to write a script, stored procedure, something else to
change the @.EndDate criteria for each IRR sp in a programatic way?
Now, I need to open each IRR sp and manually change the @.EndDate
parameter. I do have a table of IRR sp names and dates, but I am a
complete SQL novice, so I can't even so where to go next. I tried to
put dlookups in the criteria section but that doesn't seem to work.
Ryan(Ryan.Chowdhury@.gmail.com) writes:
> Now I have about 30 of these IRR sp and they all have EndDates that are
> the same or somehow systematically related (e.g. 5 are set one year
> earlier, 5 are set 3 years earlier, etc.).
> Is there a way to write a script, stored procedure, something else to
> change the @.EndDate criteria for each IRR sp in a programatic way?
> Now, I need to open each IRR sp and manually change the @.EndDate
> parameter. I do have a table of IRR sp names and dates, but I am a
> complete SQL novice, so I can't even so where to go next. I tried to
> put dlookups in the criteria section but that doesn't seem to work.
Nothing is impossible, and of course you could write a program to
do this. But for 30 procedures, I seriously doubt that it's worth the
effort. Had you said 300, it had been a different issue.
I will have to admit that I did not fully understand the setup, but if
you need to change the dates, maybe the scheme is flawed in some way.
Rather than hard-coding the values, maybe EndDate should be a parameter?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, I was told to run the IRR sp as an exec statement. I don't know
how to incorporate the EndDate as a parameter, although is it not a
parameter in the sp code above? It is just hard coded as 6/30/2005
right now.|||(Ryan.Chowdhury@.gmail.com) writes:
> Erland, I was told to run the IRR sp as an exec statement. I don't know
> how to incorporate the EndDate as a parameter, although is it not a
> parameter in the sp code above? It is just hard coded as 6/30/2005
> right now.
If I understand your description correctly, these procedures are somehow
generated. The scheme appeared strange to me, and may not be the best one.
But since I only know a fraction of what is going on in your organisation,
I can't say exactly how this should be changed.
If you think that having these 30 procedures that you need to change is
causing manageability problems, you should bring it up internally and
propose that a better solution to be developed.
As for the question you actually asked: yes, you could automate that change,
but, no, it would not be worth the effort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx