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
EXEC sp_executesql @Qry, @ParmDefinition, @GUID = @GUID
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy