Exception handling with view creation

Question

Post reply

Exception handling with view creation

arup chakraborty

Hall of Fame

Points: 3640
More actions
July 19, 2011 at 11:30 pm

#98384

Hi,
I am trying to create a view based on some table which doesn't exists. My objective is to avoid the error message, hence trying to use TRY-CATCH. But this is throwing error:
Msg 156, Level 15, State 1, Procedure vwTest, Line 3
Incorrect syntax near the keyword 'BEGIN'.
The view code is:
CREATE VIEW vwTest AS
BEGIN
BEGIN TRY
SELECT * FROM non_existence_table;
END TRY
BEGIN CATCH
PRINT ' '
END CATCH
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Use EXEC:

DECLARE @sql nvarchar(max)

SET @sql = N'

create view vwTest

as

select * from non_existence_table'

BEGIN TRY

EXEC(@sql)

END TRY

BEGIN CATCH

PRINT 'Oh no!'

END CATCH

-- Gianluca Sartori

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi Sartori,

Thanks for the response. I am aware of that method but my question is, shall I put TRY-CATCH block inside the CREATE VIEW statement?

Regards,

Arup

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

arup chakraborty (7/20/2011)
Hi Sartori,
Thanks for the response. I am aware of that method but my question is, shall I put TRY-CATCH block inside the CREATE VIEW statement?
Regards,
Arup

It's not the way to do this. I've had to do this for a report for a client. Basically some types of deployements had a table and some did not.

I had to use adhoc + sp_executesql to either select from the table or return a blank dataset with the correct column names and datatypes.

Here's a demo of that code :

DECLARE @GUID UNIQUEIDENTIFIER

SET @GUID = '00000000-0000-0000-0000-000000000000'

DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@GUID uniqueidentifier';

DECLARE @Qry NVARCHAR(4000)

IF EXISTS (SELECT * FROM sys.objects O WHERE O.name = 'Groupe FORDIA Inc_$Segment Line2' AND O.type = 'U')

BEGIN

SET @Qry = '

SELECT TOP 1

SEGL.[Segment No_]

, SEGL.[Line No_]

FROM

dbo.[Groupe FORDIA Inc_$Segment Line] SEGL

WHERE

SEGL.[Mail GUID] = @GUID

'

END

ELSE

BEGIN

SET @Qry = 'SELECT CONVERT(INT, NULL) AS [Segment No_], CONVERT(INT, NULL) AS [Line No_] WHERE 1 = 0'

END

EXEC sp_executesql @Qry, @ParmDefinition, @GUID = @GUID

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

Hi Ninja,

:):-) I have done the same way you have give. Actually just to enquiry is there any short cut or not.

Thnaks ton,

Arup

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

arup chakraborty (7/20/2011)
Hi Ninja,
:):-) I have done the same way you have give. Actually just to enquiry is there any short cut or not.
Thnaks ton,
Arup

No I think preventing here is better than catching the error. That was for a report in SSRS. Maybe there's a better way for a richer GUI, but right now I don't have a better solution.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

arup chakraborty

Hall of Fame

Points: 3640

July 20, 2011 at 7:10 am

#1355566

Thanks Ninja