Howto Create a SQL Conditional View

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' then

begin

Create 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 )

BEGIN

CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE

END

This command fails with the error:

Messagge 156, livello 15, stato 1, riga 4

Incorrect 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 1

Incorrect syntax near 'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'.

The solution is to use the stored procedure sp_executesql:

sp_executesql N'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'

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!