Here is a script on how to create a conditional view based on a parameter
Sometimes things seems simple, this instruction is valid:
if @condition = '1' thenbeginCreate Table...end
Here is the equivalent instruction, where Table is substituted by View:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vwTest]') AND OBJECTPROPERTY(id, N'IsView') = 1 )BEGINCREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLEEND
This command fails with the error:
Messagge 156, livello 15, stato 1, riga 4Incorrect syntax near the keyword 'VIEW'.
The alternative is to use the “exec” command:
exec N'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'
But here is the sad message:
Messagge 102, livello 15, stato 1, riga 1Incorrect syntax near 'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'.
The solution is to use the stored procedure sp_executesql:
Don’t forget , if you are passing the command using a parameter, to define it as ntext/nchar/nvarchar, here is the strange message if you don’t use this types:
Incorrect syntax near ...
Hope it helps!