Post reply

Can't create a view inside and IF block?

  • May 5, 2011 at 9:04 am

    #237666

    So, I have a database object creation/update script that has a problem with creating a view inside and IF block:

    if 1=1 begin

    create view dbo.test

    as

    select * from sometable

    end

    This returns an error saying Incorrect syntax near the keyword 'view'.

  • May 5, 2011 at 9:30 am

    #1320493

    From Books Online: "CREATE VIEW must be the first statement in a query batch. "

    What are you really trying to do by conditionally creating a view?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • May 5, 2011 at 9:34 am

    #1320496

    if 1=1

    begin

    exec('

    create view dbo.test

    as

    select * from sometable

    '

    end

  • May 5, 2011 at 9:35 am

    #1320497

    My script s designed to update an existing database so I need to check for existence first. It's an indexed view so I don't really want to drop and recreate it every time either but I think that that is my only option.

  • May 5, 2011 at 9:42 am

    #1320503

    why can't you just issue an ALTER VIEW command? don't you already know it exists, or is this script a create/recreate in case of an empty database?

    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!

  • May 5, 2011 at 9:48 am

    #1320507

    buddy__a (5/5/2011)

    My script s designed to update an existing database so I need to check for existence first. It's an indexed view so I don't really want to drop and recreate it every time either but I think that that is my only option.

    Why do you need to check for existence of the view before you do an UPDATE? How likely is it that the view will not exist?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • May 5, 2011 at 9:52 am

    #1320509

    buddy__a (5/5/2011)

    My script s designed to update an existing database so I need to check for existence first. It's an indexed view so I don't really want to drop and recreate it every time either but I think that that is my only option.

    What are you checking for the existence of: the view or the index?

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

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