SQLTeam.com | Weblogs | Forums

To ignore syntax error and continue executing back and forth between try and catch block

My sql version is

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)


/* Run this part only once */

--==========================================================
--      Create and populate a Tally table
--==========================================================
--===== Conditionally drop 
     IF OBJECT_ID('dbo.Tally') IS NOT NULL 
        DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100


-- drop and create sample table
     IF OBJECT_ID('dbo.date256') IS NOT NULL 
        DROP TABLE dbo.date256

create table date256 (N int primary key, 
					sqlcmd varchar(max),
					result varchar(max))
					
-- populate the table
	insert date256 (N , sqlcmd )
	select N ,'convert(varchar, getdate(),' + '' 
	         + convert(varchar,N) +'' + ')'
			 + ' where N = ' + convert(varchar,N) 
	from Tally
	where N <=140
--=======one time run of Schema and sample data finished ==============

Starting procedure



--========= building a procedure ==============

declare @d datetime=getdate()
declare @N int=1 , @sql varchar(max)=''
set nocount on

BEGIN TRY

	while @N < 141
	begin  -------- loop start
		select @sql = 'update date256 set result = ' + sqlcmd
		from date256
		where N= @N 

		--print @sql
		exec (@sql)
                /*
                  when the counter N reaches 16
                  @sql will have syntax error
                  will go to catch block and won't come back
                  i want it to come back and execute
                  the rest of the following lines
                  and loop again untill the loop dies
                  after 140 iteration
                 */
		set @N = @N +1 
	end -- loop ends

END TRY


BEGIN CATCH
 Print
       'Please ignore the error and go back
       from where you have come and execute 
       the rest of the codes. Thnaks'
  delete date256 where N=@N
END CATCH
select *from date256

What I want
I want to learn if it is possible to ignore syntax error
and continue executing back and forth
between Try and Catch block

If you run the above procedure 99 times
then there will be no error and the result
will be as under

N sqlcmd result
1 convert(varchar, getdate(),1) where N = 1 10/15/20
2 convert(varchar, getdate(),2) where N = 2 20.10.15
3 convert(varchar, getdate(),3) where N = 3 15/10/20
4 convert(varchar, getdate(),4) where N = 4 15.10.20
5 convert(varchar, getdate(),5) where N = 5 15-10-20
6 convert(varchar, getdate(),6) where N = 6 15 Oct 20
7 convert(varchar, getdate(),7) where N = 7 Oct 15, 20
8 convert(varchar, getdate(),8) where N = 8 12:41:45
9 convert(varchar, getdate(),9) where N = 9 Oct 15 2020 12:41:45:020PM
10 convert(varchar, getdate(),10) where N = 10 10-15-20
11 convert(varchar, getdate(),11) where N = 11 20/10/15
12 convert(varchar, getdate(),12) where N = 12 201015
13 convert(varchar, getdate(),13) where N = 13 15 Oct 2020 12:41:45:020
14 convert(varchar, getdate(),14) where N = 14 12:41:45:023
20 convert(varchar, getdate(),20) where N = 20 2020-10-15 12:41:45
21 convert(varchar, getdate(),21) where N = 21 2020-10-15 12:41:45.023
22 convert(varchar, getdate(),22) where N = 22 10/15/20 12:41:45 PM
23 convert(varchar, getdate(),23) where N = 23 2020-10-15
24 convert(varchar, getdate(),24) where N = 24 12:41:45
25 convert(varchar, getdate(),25) where N = 25 2020-10-15 12:41:45.033
100 convert(varchar, getdate(),100) where N = 100 Oct 15 2020 12:41PM
101 convert(varchar, getdate(),101) where N = 101 10/15/2020
102 convert(varchar, getdate(),102) where N = 102 2020.10.15
103 convert(varchar, getdate(),103) where N = 103 15/10/2020
104 convert(varchar, getdate(),104) where N = 104 15.10.2020
105 convert(varchar, getdate(),105) where N = 105 15-10-2020
106 convert(varchar, getdate(),106) where N = 106 15 Oct 2020
107 convert(varchar, getdate(),107) where N = 107 Oct 15, 2020
108 convert(varchar, getdate(),108) where N = 108 12:41:45
109 convert(varchar, getdate(),109) where N = 109 Oct 15 2020 12:41:45:033PM
110 convert(varchar, getdate(),110) where N = 110 10-15-2020
111 convert(varchar, getdate(),111) where N = 111 2020/10/15
112 convert(varchar, getdate(),112) where N = 112 20201015
113 convert(varchar, getdate(),113) where N = 113 15 Oct 2020 12:41:45:037
114 convert(varchar, getdate(),114) where N = 114 12:41:45:037
120 convert(varchar, getdate(),120) where N = 120 2020-10-15 12:41:45
121 convert(varchar, getdate(),121) where N = 121 2020-10-15 12:41:45.037
126 convert(varchar, getdate(),126) where N = 126 2020-10-15T12:41:45.037
127 convert(varchar, getdate(),127) where N = 127 2020-10-15T12:41:45.040
130 convert(varchar, getdate(),130) where N = 130 28 ??? 1442 12:41:45:040PM
131 convert(varchar, getdate(),131) where N = 131 28/02/1442 12:41:45:040PM

Hope I am clear.

Why? instead of convert use try_convert

1 Like

Yes that is the correct way.

I just want to learn, if it is possible to continue
executing despite error
not necessarily syntax error

For practical purpose:
When I read attendance machine in out data
and insert into my table
there are lots of similar time (may be machine bug)
I have PK error and I wish
instead of checking if exists I could continue

please post error and offending code. isn't it almost 2am where you are at?

I don't think there is any way to do that in SQL 2008.

1 Like

is it possible in 2012
I have 2012 at office.

:laughing: :rofl:

1 Like

I can only go by what you tell us. I'm not a mindreader on what you have.