Creating a View using IF statements

Question

Post reply

Creating a View using IF statements

rbaskett

SSC Enthusiast

Points: 168
More actions
September 10, 2010 at 1:31 pm

#142687

1 | DECLARE @DBNAME VARCHAR(50)
2 |
3 | SELECT @DBNAME=DB_NAME()
4 |
5 | print @DBNAME
6 |
7 | if(@DBNAME='mydb1')
8 | begin
9 | create view view as select * from [instance1].database1.dbo.table with(nolock)
10| end
11| if(@DBNAME='mydb2')
12| begin
13| create view view as select * from [instance2].database2.dbo.table with(nolock)
14| end
generates an error on running:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'view'.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Try this (using dynamic sql..feel free to modify the values to your needs of course)

DECLARE @DBNAME VARCHAR(50)

DECLARE @sql nvarchar(250)

SELECT @DBNAME=DB_NAME()

print @DBNAME

if(@DBNAME='master')

begin

SET @sql = 'create view [view] as select * from dbo.sysprocesses with(nolock)'

EXEC(@SQL)

end

if(@DBNAME='msdb')

begin

SET @sql = 'create view [view] as select * from dbo.sysjobs with(nolock)'

EXEC(@SQL)

end

rbaskett SSC Enthusiast Points: 168 More actions · Answer 2

Dynamic sql works, just curious why pos (plain old sql) doesnt

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Probably the same reason it wouldn't work if you tried to create a SP that way. It needs to be the first line in the batch (i.e. need a GO before it). Can't find any mention of a View needing to be created this way, but that's what it looks like.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic984072-149-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rbaskett SSC Enthusiast Points: 168 More actions · Answer 5

ya, sorry bout that. its posted in the proper area now.