Click here to Skip to main content
13,448,005 members (55,950 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I have a stored procedure it contained many select statements. I want to execute that procedure continuously if fails in between it should not stop
all select should it possible ..?

What I have tried:

In which way we can achieve this ..
Posted 19-Jun-17 0:43am
Updated 19-Jun-17 13:26pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Of course you can program an endless loop inside the procedure, for example WHILE 1 = 1.

However, I cannot imagine a situation when this would make sense. For example
- The control does not return to the calling program until the execution ends
- The execution will be ended by force when a timeout occurs

So what I suggest is that you consider some other kind of approach which suits you, depending on the problem.
Member 11337367 19-Jun-17 7:09am
Suppose in this
select 5-'a'
select 4+3

if first statement thorws an error it should go to second statment
Andy Lanng 19-Jun-17 7:13am
You can use try block
Wendelius 19-Jun-17 7:17am
Okay, so the problem is that you want to continue even if an error pccurs.

As Andy Lang already pointed out you can utilize error handling. Have a look at TRY...CATCH (Transact-SQL) | Microsoft Docs[^] and RAISERROR (Transact-SQL) | Microsoft Docs[^]
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Maybe you can use Trigger(s) instead, see:
[Triggers -- SQL Server]
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

If you are saying stored procedure should complete its execution inspite of errors then write you logic in try - catch block (as written in comments). when error occurs, in the catch block you can put all your errors ,say in some error_table , detailing error message and if any key columns you want to store and it will keep running the procedure.

later you can do select * from you error_table to see the errors stored proc encountered.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.180318.3 | Last Updated 19 Jun 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100