March 16, 2010 at 1:49 pm
I have a stored procedure I want to be able to conditionally execute. I want to be able to create a query that will look for specific settings, and if true to execute the stored procedure.
If Result = Completed and DateTime > CONVERT(varchar(10),GETDATE(),101))
Then EXECUTE uspProcedure
I want to be able to do something along these lines. After I can set it up in a basic T-SQL query, I want to be able to write this into a SQL Agent Job.
Thanks in advance for any guidance.
March 16, 2010 at 2:07 pm
IF <conditions>
BEGIN
EXEC TheStoredProc
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 2:17 pm
Hmm, I am still having issues when I try to reference the table the values are in. Here is the query/errors:
IF (ProcessID = 2 and EndTime > CONVERT(varchar(10),GETDATE(),101))
and Result = 'Completed'
From tblProcessLog)
Begin
EXEC uspMortgageData
END
ERROR:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'From'.
Thanks again.
March 16, 2010 at 2:27 pm
More details needed. Perhaps you can take a step back and explain the slightly larger picture?
What defines which row of a table those values have to come from? Or are you just checking to see if a row with those conditions exists? If so, use the EXISTS keyword. IF EXISTS (select statement)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2010 at 2:28 pm
It seems like you're trying to do an existence check and run the proc based on the result. If so, the following code should work.
Note: it checks if there is at least one row in table tblProcessLog that matches all conditions in the WHERE clause. If so, it will execute uspMortgageData. Otherwise it will not.
IF EXISTS(
SELECT 1
FROM tblProcessLog
WHERE ProcessID = 2
AND EndTime > CONVERT(VARCHAR(10),GETDATE(),101))
AND RESULT = 'Completed'
)
BEGIN
EXEC uspMortgageData
END
March 16, 2010 at 3:20 pm
Thank you for the help. lmu92 your suggestion worked.
The final stage is to create this as a SQL Agent job.
I now have the following query as the only step within the job:
IF EXISTS(
Select 1
From tblProcessLog
Where ProcessID = 2 and EndTime > CONVERT(varchar(10),GETDATE(),101)
and Result = 'Completed'
)
Begin
EXEC uspMortgageData
END
I want for it to be set up that if the row I'm looking for does not exist to retry according to the advanced settings for the step. Will this current code support this? Or are there any changes I'd need to make?
Thanks again.
March 16, 2010 at 3:30 pm
If you want it to retry the step you have to define the status "failed".
That can either be done within your sproc uspMortgageData or you define an raiserror() statement as an ELSE condition. This will cause the step to fail leading to a retry.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply