Conditionally CREATE a VIEW in a script

Hi,

I would like to create a view depending on a condition check first. However, I do not seem to the able to put a 'CREATE VIEW' within an IF statement. The following example demonstates what I am trying to achieve (please excuse the triviality of the example):

IF NOT col_length('authors','city') IS NULL

BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
END
ELSE
BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
END

When I try to parse/run this I get the following syntax error:

"Incorrect syntax near the keyword 'VIEW'."

Any help would be much appreciated.

Thanks.

Try the code below.

Chris

Code Snippet

DECLARE @.sqlstring NVARCHAR(4000)

IF NOT col_length('authors', 'city') IS NULL
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address + '', '' + city) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END
ELSE
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END

|||

I think this looks misguided. Rather than changing the view that is is created dynamically, I think you need to change the view permanently so that both views can be represented by a singular view that uses CASE construct. Hang on and if I don't get you an example, I imagine someone else will.

Maybe something like this:

create view testView
as

select au_fname + ' ' + au_lname
as fullName,
address
+ case when len(rtrim(city)) = 0
then ''
else ', ' + city
end
as address
from authors

go

select * from testView

/*
fullName address
--
Johnson White 10932 Bigge Rd., Menlo Park
Marjorie Green 309 63rd St. #411, Oakland
Cheryl Carson 589 Darwin Ln., Berkeley
*/

|||

Hi Chris,

I had thought about doing that but the real view is quite large and I was trying to avoid dealing with string manipulation but I suppose its just two single quotes for ant existing single quotes.

Thanks.

Smoc

|||

Hi Kent,

Thanks for the response but that will not work if the column does not exist in the table which is the reason I want to conditionally create 1 of 2 possible views. In the simplistic example, I want to handle the situation when the column 'city' may not be in the authors table.

I realise that i could use the col_length function instead to achieve the result you have proposed. I was just wondering why I could have two 'clean' view definitions in a script contained within an IF statement.

Regards,

Smoc

|||

Just thinking out loud really, but could you programatically add the City column to the authors table if the column doesn't exist? That way, going forward, you'd only have one version of the View to maintain.

Chris

|||

Hi Chris,

We have an application that is using a database that we have no control over and no authority to change. We have discovered some differences between schemas of different clients who have this database. The differences are not critical and we hope to handle it at the view level. Other than that we would do as you suggested.

I'm just supprised that I can do a DROP command but not a Create View command in an IF statement.

Smoc

|||

You can't create view/procedure/function/trigger inside or mid of your batch.

These create scripts should be the first line of the batch.

In IF batch you can put only the Drop view/procedure/function/trigger.

The only possible way is using dynmaic sql.

|||

Thanks for the clarification.

I will probably use the dynamic sql that you have suggested and as was also suggested in an earlier thread.

Thanks.

|||There is a neat trick to achieve just what you want . Check out this example:

-- If column doesn't exists, does not create the view that use it
IF col_length('authors','city') IS NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off
go
-- If column exists, does not create the view without it
IF col_length('authors','city') IS not NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off

You only need to carefully choose your conditions because they have to be "reversed", in a way. Still, it is a proven and reliable approach.