Bookmark and Share

Tuesday, April 21, 2009

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!