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.
