SQL Server Performance

View Error

Discussion in 'General Developer Questions' started by ranjitjain, Sep 12, 2005.

  1. ranjitjain New Member

    Hi,
    What's wrong here???
    I'm getting error incorrect syntax near view?????????

    if (object_id(N'[vw_Factday]') is not null)
    begin
    create view vw_Factday
    as
    select timeid, starttimeUTC, endtimeUTC
    from aaiTimePeriod
    where partition='h'
    end

    Thanx
  2. SQL_Guess New Member

    Create view must be the first command in batch. This is a frustration I've had before.
    Also, your logic may be wrong - if the view exists, you want to create it?

    You will probably need to do something like:



    if (object_id(N'[vw_Factday]') is not null)
    begin
    drop view vw_Factday
    end
    else
    print 'View Not Found'
    go

    create view vw_Factday
    as
    select timeid, starttimeUTC, endtimeUTC
    from aaiTimePeriod
    where partition='h'

    go
    if (object_id(N'[vw_Factday]') is not null)
    begin
    Print 'View found : created Successfully'
    end
    else
    print 'View Not Found : Please check'



    Panic, Chaos, Disorder ... my work here is done --unknown
  3. dineshasanka Moderator

    if (object_id(N'[vw_Factday]') is not null)
    drop view vw_factory
    go

    create view vw_Factday
    as
    select timeid, starttimeUTC, endtimeUTC
    from aaiTimePeriod
    where partition='h'



    no need for begin and end

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  4. ranjitjain New Member

    Dinesh,
    I want to implement it inside the SP.
    Will it work it's still gives the same error.
    Thanx.
  5. johnson_ef Member

    Dear Ranjit,

    I think, we can re-write the code like this.


    IF OBJECT_ID ('vw_Factday', 'view') IS NOT NULL
    DROP VIEW vw_Factday ;
    GO
    CREATE VIEW vw_Factday
    AS
    select timeid, starttimeUTC, endtimeUTC
    from aaiTimePeriod
    where partition='h'
    GO


    Is this you were trying??

    -Johnson
  6. Madhivanan Moderator

    >>I want to implement it inside the SP.

    Why do you want to create view using SP?

    Madhivanan

    Failing to plan is Planning to fail
  7. mmarovic Active Member

    If you really want it, then use dynamic sql.
  8. ranjitjain New Member

    Hi,
    I want to create a view only when its not there.
    I'm referring to the view in many SP's. So i want to confirm whether the view is there or not.
    So i dont want to create it everytime.
    Once created i only want to check whether present or not.
  9. Adriaan New Member

    The first question must be how you think your view could be dropped from your system. You create the system, and you must be able to say at one point: here's the list of all permanent objects, and this view is on that list.
  10. SQL_Guess New Member

    Hmm - been a while since I was on.

    I agree - you shouldn't be creating a view in the SP - it should exist, since it is a permanent object.

    @Dinesh: you're quite right - the begin and end's weren't necessary - I had them there since typically, I would have a comment explaining that I did smoething (like dropping a view).

    Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page