December 8, 2016 at 1:30 am
Hi I'm running a stored procedure that executes other stored procedure based on what's needed. My issue is that sometimes some of the stored procedures fail come back with an error and stop the whole code.
DECLARE @sp-2 varchar(80)
DECLARE @cnt INT = 1
DECLARE @RunID INT
SET @RunID = (SELECT MAX(RunID)+1 as RunID FROM [RISE_DATA].[dbo].[tb_CompaniesToRun])
------------------------- EXECUTE ALL THE STORED PROCEDURES THAT NEED TO RUN -------------------
WHILE @cnt < (SELECT DISTINCT MAX(Target_ID)+1 as SPs FROM [RISE_DATA].[dbo].[tb_CompaniesToRun] WHERE RunID = @RunID)
BEGIN
SET @sp-2 = '[RISE].[dbo].'
SET @sp-2 = (SELECT DISTINCT @sp-2 + [Target_StoredProcName] as SPsFROM [RISE_DATA].[dbo].[tb_CompaniesToRun] WHERE Target_ID = @cnt AND RunID = @RunID)
EXEC (@sp)
SET @cnt = @cnt + 1
END
I googled this and found that putting a GO in between executions can solve this issue, but when I change my code with the Go it didn't work at all.
So I tried testing some ideas but all failed.
This code does exactly what I need
EXEC [RISE_DATA].[dbo].[sp1]
GO
EXEC [RISE_DATA].[dbo].[sp2]
GO
EXEC [RISE_DATA].[dbo].[sp3]
GO
EXEC [RISE_DATA].[dbo].[sp4]
GO
EXEC [RISE_DATA].[dbo].[sp5]
GO
But if I try to replicate that in code it comes back with an error
DECLARE @a nvarchar(888)
SET @a = N'EXEC [RISE_DATA].[dbo].[sp1] ' + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
+ 'EXEC [RISE_DATA].[dbo].[sp2] ' + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
+ 'EXEC [RISE_DATA].[dbo].[sp3] ' + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
+ 'EXEC [RISE_DATA].[dbo].[sp4] ' + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
+ 'EXEC [RISE_DATA].[dbo].[sp5] ' + CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
--PRINT @a
EXEC sp_executesql @a
the error is:
Msg 8146, Level 16, State 1, Procedure sp1, Line 2
Procedure sp1 has no parameters and arguments were supplied.
Msg 8146, Level 16, State 1, Procedure sp2, Line 2
Procedure sp2 has no parameters and arguments were supplied.
Msg 8146, Level 16, State 1, Procedure sp3, Line 2
Procedure sp3 has no parameters and arguments were supplied.
Msg 8146, Level 16, State 1, Procedure sp4, Line 2
Procedure sp4 has no parameters and arguments were supplied.
Msg 8146, Level 16, State 1, Procedure sp5, Line 2
Procedure sp5 has no parameters and arguments were supplied.
I think that it thinks about the 'GO' as a parameter. So my question is how can I avoid that?
Or how can I make my code ignore the failed procedures and move on to the next one?
Thanks for any suggestions
December 8, 2016 at 2:46 am
Thom A (12/8/2016)
What you're looking for is BEGIN TRY:
BEGIN TRYEXEC thisspdoesnotexist_sp;
END TRY
BEGIN CATCH
RAISERROR('Enter your error message here',1,0);
END CATCH
That only points out the error but does not continue the execution of the rest
December 8, 2016 at 2:51 am
davidvarga086 (12/8/2016)
Thom A (12/8/2016)
What you're looking for is BEGIN TRY:
BEGIN TRYEXEC thisspdoesnotexist_sp;
END TRY
BEGIN CATCH
RAISERROR('Enter your error message here',1,0);
END CATCH
That only points out the error but does not continue the execution of the rest
Yes it does. How have you used your BEGIN TRY? Around the while thing? You should be doing it around each point of failure.
For example:
BEGIN TRY
EXEC thisspdoesnotexist_sp;
END TRY
BEGIN CATCH
RAISERROR('Enter your error message here',1,0);
END CATCH
BEGIN TRY
EXEC master.dbo.sp_readerrorlog 0, 1,'TEMPDB';
END TRY
BEGIN CATCH
RAISERROR('Enter your error message here',1,0);
END CATCH
This will raise an error for the thisspdoesnotexist_sp, but still run sp_readerrorlog
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 1:34 am
Thanks that worked I still have to do some testing but it looks good so far
December 9, 2016 at 1:52 pm
On a separate note.
GO is a batch separator.
https://msdn.microsoft.com/en-us/library/ms188037.aspx
and the definition of a batch is...
"a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution." emphasis added
This means "GO" divides your SQL into segments that are sent to the server for processing separately. Including GO within your SP will cause it to fail because it can't send part of the SP definition (or part of the dynamic expression you've created) to the server without a syntax error.
DECLARE @sql VARCHAR(50)= 'select 1;
GO'
EXEC (@sql);Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Biggest surprise is usually that GO is not a valid SQL keyword. It cannot be executed on the server.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply