Post reply

Creating A View within an IF statement

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

  • September 10, 2010 at 1:44 pm

    #1219453

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

  • 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!

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

Viewing 4 posts - 1 through 3 (of 3 total)

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