Why is this If statement with a create view not syntactically correct?

  • If Not Exists(Select * From Information_Schema.Views Where Table_Schema = 'Div2' And Table_Name = 'Categories')

      Create View Div2.Categories As

        Select

            CT.Name                 Name

          , IsNull(RCC.Parent, -1)  Parent

          , RCC.Description         Description

          , RCC.LevelNo             LevelNo

          , RCC.Fixed               Fix

          , RCD.Rank                Rank

          , RCD.Visible             Visible

        From

          ConstType CT

          Join CategoriesCommon RCC On RCC.ConstTypeId = CT.Id

          Join CategoriesDivision RCD On RCD.CategoriesCommonId = RCC.Id

        Where

          (RCD.DivisionId = 2)

    I'm too tired, maybe I'll be lucky and someone will see a syntax error.

    Thanks for your help in advance,

    Richard

  • When I use Query Analyzer, it generates a BEGIN and END statement. Does this matter?

    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    BEGIN

    CREATE TABLE [tbl] (

    [field1] [decimal](11, 0) NULL ,

    [field2] [varchar] (50) NULL

    ) ON [PRIMARY]

    END



    Michelle

  • you forgot begin and end block

  • Hi,

    I found out why. It turns out that "Create View" has to be the first statement of a batch. Its written up under "Create View" in Books Online. The following works:

    If Exists(Select * From Information_Schema.Views Where Table_Schema = 'Div2' And Table_Name = 'Categories')

      Drop View Div2.Categories

    go

      Create View Div2.Categories As

        Select

            CT.Name                 Name

          , IsNull(RCC.Parent, -1)  Parent

          , RCC.Description         Description

          , RCC.LevelNo             LevelNo

          , RCC.Fixed               Fix

          , RCD.Rank                Rank

          , RCD.Visible             Visible

        From

          ConstType CT

          Join CategoriesCommon RCC On RCC.ConstTypeId = CT.Id

          Join CategoriesDivision RCD On RCD.CategoriesCommonId = RCC.Id

        Where

          (RCD.DivisionId = 2)

    Have a nice weekend,

    Richard

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

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