Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards