Creating A View within an IF statement

• rbaskett

  SSC Enthusiast

  Points: 168

  More actions

  September 10, 2010 at 1:37 pm

  #142688

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

• rbaskett

  SSC Enthusiast

  Points: 168

  More actions

  September 10, 2010 at 1:44 pm

  #1219453

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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 10, 2010 at 1:49 pm

  #1219459

  rbaskett (9/10/2010)

  ---

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

  it's the compiler rules...you've probably seen it:

  Msg 111, Level 15, State 1, Line 57

  'CREATE VIEW' must be the first statement in a query batch.

  same rules apply for trigger,procedure,function as well....

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• rbaskett

  SSC Enthusiast

  Points: 168

  More actions

  September 10, 2010 at 1:52 pm

  #1219462

  yep - seen that error before - still dont understand the need for that but microsoft didnt ask me ...