Scraping parameter values from stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Scraping parameter values from stored procedures

Author

Topic

stirtonm
Starting Member

3 Posts

Posted - 2012-09-04 : 02:57:41

Scraping parameter values from stored procedures
------------------------------------------------
Good morning. Please help me. I'm using MS SQL 2008.
I'm building a proof of concept for a flexible cost calculator.
My aim is to store cost component calculations in a table. These components are in the form of lookups based on
parameters.
At a high-level I want to call a stored proc (from a number of existing stored procs) that will
query my cost calc tables, retrieve the parameters needed for the calc & perform the calc. The calcs however need
parameter values from the calling stored procs. How do I dynamically assign these parameters & THEIR VALUES to my
cost calc stored proc? I'll illustrate what I'm trying to achieve by way of an example:

-- dbo.Cost_calc table
Description varchar(20), Parameters nvarchar(100), SQl_Code nvarchar(100)
'Base Cost', '@Product', 'Select dbo.fn_GetBaseCost(@ProductValue)'
'Customer Discount', '@Customer_ID', 'Select dbo.fn_GetCustomerDiscount(@Customer_IDValue)'

-- Calling Stored Proc Sample
Create stored proc dbo.CalcProjectedProfit (
@Customer_ID Int
, @Product Char(3)
, @NextYearEnd DateTime
, @PurchaseDate DateTime
)
AS

-- do some stuff

-- Calculate current cost
Declare @Costcalc_Temp Table (
Description varchar(20)
, Parameters nvarchar(100)
, SQl_Code nvarchar(100)
, Parameter_Values nvarchar(100) -- we want to populate this column by "scraping" the values from the calling proc's input parameters
)

Insert Into @Costcalc_Temp
Select * From dbo.Cost_calc

/*
At this point I have 2 parameters in my temp table for which I need values (@Product and @Customer_ID).
The values for these are available (or passed in) in the calling proc, CalcProjectedProfit.
Ideally I want to do something like:

Update CT
Set CT.Parameter_Values = CT.Parameters -- Value(CT.Parameters)
From @Costcalc_Temp AS CT

-- which will translate to the following for the 1st row of my temp table
Update CT
Set CT.Parameter_Values = @Product
From @Costcalc_Temp AS CT

*/

-- do some stuff

If possible, I do not want to explicitly set parameter values in each calling stored proc. I'm dealing with a legacy app with
business logic residing in stored procs. I simply want to add a common block of code to each calling stored proc (& later-on deprecate the legacy calcs).
To illustrate my challenge, I've greatly simplified the example & cut corners with best practice syntax.
Any guidance will be highly appreciated.
Thank you

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-04 : 10:59:38

Perhaps have your calculation proc take a table as a param, then you can simply pass in all the params from the calling proc in a table, and parse it in your calc proc.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

stirtonm
Starting Member

3 Posts

Posted - 2012-09-06 : 03:40:39

Thanks for the reply.

stirtonm
Starting Member

3 Posts

Posted - 2012-09-06 : 04:07:56

Oops. Thanks for the reply.
You gave me an idea for a workaround. Thank you!
It still means that I cannot dynamically populate my table parameter.
It would have been nice if SQL allowed one to get the properties (including the value) of a parameter, e.g. in VB, one can call Param.Value .
The closest thing SQl has appears to be SQL_VARIANT_PROPERTY which basically gives you the datatype.
Thanks again...I'll use the workaround in the meantime.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-06 : 11:49:01

I am glad you arrived at an answer.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources