November 29, 2011 at 4:35 pm
I have a SQL Agent job which runs a query and does other things depending on if the query returns rows.
If Exists(Select * from TABLE WHERE .....)
Begin
...
End
Now I need to do a different query depending on the time of day. I've created a variable that is set to to the hour the code is run
Declare @SQL as varchar(500)
If Datepart(hour(getdate()) < 8
Set @SQL = 'select.....
Else
Begin
Set @SQL = 'select somethingelse from...
End;
EXEC(@SQL)
My code above does run the correct query for the time of day, but if I try to use EXISTS, it doesn't work
If Exists (EXEC(@SQL))
Begin
From what I'm reading, this is because you can't use dynamic SQL inside of a function. So what is the best approach to do what I want to do?
November 29, 2011 at 5:17 pm
Do the statements really need to be dynamic? Or would the statement always be the same for the same hour?
Maybe you could provide a little bit more detail on exactly what you would want the statements to be based on the specific conditions you need to evaluate.
November 29, 2011 at 5:51 pm
I want the query used between midnight & 7am to be slightly different from the query used other times.
I could have two versions of the whole thing inside
If Datepart(hour,GETDATE()) < 7
...
ELSE
...
But that seems like a lot of duplicated code and two places to change it if there are other tweaks.
November 29, 2011 at 8:12 pm
It would be nice to know the nature of what the dynamic code does, so we could completely understand the problem, but here are a couple of thoughts to maybe steer you in the right direction.
1) You could use sp_executeSQL instead of EXEC to run your dynamic SQL. This would allow you to define an OUTPUT parm that could be returned to your parent code and tested in lieu of the EXISTS.
2) If you want to create a function that is going to be used by only this one job, you can dynamically create and execute ALTER statements to change the function before you run it each time. This is a MUCH cruder solution than simply switching to sp_executeSQL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 1, 2011 at 2:10 pm
Another thing I'm trying is to put a list of values into a variable and using in an IN clause, as in
IF EXISTS(SELECT COLUMN_A, COLUMN_B FROM TABLE WHERE COLUMN_A IN (@VARIABLE))
But that always returns no rows, even when it does return rows with the actual values are in the IN clause.
I'm starting to try the sp_executesql approach now. I'm a bit concerned because my query may return multiple rows while the example in BOL returns one row only.
December 1, 2011 at 2:25 pm
Does the version of the query where you say there would be a lot of duplicated code actually work as expectecd?
Dave
December 1, 2011 at 3:30 pm
I haven't tested it yet, but it currently works with a single static query.
The duplicate version would look like this:
IF DATEPART(HOUR,GETDATE()) > 7
(all the current code that works now)
ELSE
(copy & paste the working code above, then tweak the list of values in an IN clause)
December 1, 2011 at 3:36 pm
Can you test both static queries and post the results of both?
Dave
December 1, 2011 at 4:10 pm
Just to make sure you understand a couple of points:
1. Dynamic SQL (including sp_executesql) is not allowed in a TSQL function. If you need to use it, code it in a stored procedure.
2. The following is not going to work; SQL Server just sees @VARIABLE as a single value, not as a comma delimited list.
SELECT COLUMN_A, COLUMN_B FROM TABLE WHERE COLUMN_A IN (@VARIABLE))
I'm not real clear on exactly what you are trying to do, so if you can provide a better explanation, then someone can probably find a good solution.
December 1, 2011 at 5:35 pm
Here it is.. Ths is a SQL Agent Job that monitors Reports jobs that are printed through several services. We want to be notified if any go longer than 90 minutes without processing a report because they might be hung. What I came up with is below. But between midnight & 7am service 7 isn't used very much, sometimes causing false alarms (pages that needlessly wake people up). What is the best way to run a slightly different query during certain hours?
If Exists(select distinct rh.ReportService, Max(CreateTime) 'Most Recent', MAX(Jobid) 'Last Job'
from ReportHistory rh
where (select Max(CreateTime) from ReportHistory rh2
where rh2.ReportService in ('RPTSVC_2','RPTSVC_3','RPTSVC_4','RPTSVC_5','RPTSVC_6','RPTSVC_7','RPTSVC_8')
and rh.ReportService = rh2.ReportService) < DATEADD(MINUTE,-90,GetDate())
group by ReportService)
Begin
Exec msdb.dbo.sp_send_dbmail
-- sp_send_dbmail parameters
End
December 1, 2011 at 5:35 pm
CELKO (12/1/2011)
The Dynamic SQL you posted is an admission of failure and incompetence.
Don't worry Celko, your continued posting is admission of failure on the part of evolution to remove the volume of bone from the skull that apparently keeps pressing on the regions of the brain where a hint can be taken.
You and the OP are even footing now.
Edit: Typo
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2011 at 5:46 pm
dan-572483 (12/1/2011)
What is the best way to run a slightly different query during certain hours?
I wouldn't vary the query, just vary the association. Easiest way to do this is with a filter table. Check the code below for an example of what I'm talking about
CREATE TABLE #TimedReportChecks (ReportService VARCHAR(20))
INSERT INTO #TimedReportChecks
SELECT * FROM
(SELECT 'RPTSVC_2' UNION ALL
SELECT 'RPTSVC_3' UNION ALL
SELECT 'RPTSVC_4' UNION ALL
SELECT 'RPTSVC_5' UNION ALL
SELECT 'RPTSVC_6' UNION ALL
SELECT 'RPTSVC_8')
IF CAST( DATEPART( hh, getdate()) AS INT) BETWEEN 0 and 7
BEGIN
INSERT INTO #TimedReportChecks
VALUES( 'RPTSVC_7')
END
If Exists(select distinct
rh.ReportService,
Max(CreateTime) 'Most Recent',
MAX(Jobid) 'Last Job'
from
ReportHistory rh
where
(select
Max(CreateTime)
from
ReportHistory rh2
JOIN
#TimedReportChecks AS t
ONt.ReportService = rh2.ReportService
where
rh.ReportService = rh2.ReportService
) < DATEADD(MINUTE,-90,GetDate())
group by
ReportService)
Begin
Exec msdb.dbo.sp_send_dbmail
-- sp_send_dbmail parameters
End
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2011 at 12:38 pm
Creating a temp table and doing a in(select * from #temptable) turns out to be the appaoch that works.
Thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply