July 19, 2011 at 11:30 pm
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
July 20, 2011 at 2:35 am
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
July 20, 2011 at 6:09 am
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
July 20, 2011 at 6:19 am
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
July 20, 2011 at 6:55 am
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
July 20, 2011 at 7:01 am
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.
July 20, 2011 at 7:10 am
Thanks Ninja
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply