[RESOLVED] SQL Server 2005 Error On Create View

[GaryMazzone]

I'm having an problem with this SQL:

IF NOT EXISTS(SELECT [name] FROM SYS.VIEWS WHERE [name] = 'Vw_RoomBlock_ReportingRoomTotals_1' AND schema_id = (SELECT schema_id FROM SYS.SCHEMAS WHERE [name] = 'Reporting'))
BEGIN
    Create  VIEW [Reporting].[Vw_RoomBlock_ReportingRoomTotals_1] 
    AS 
 
    Select 
        distinct(RoomBlock.RoomBlockId), 
        EntityType.Name as EntityType,
        RoomBlock.EntityId,
        RoomBlock.EntityTypeId, 
        RoomBlock.StartDate, 
        temp.RoomRevenue as RoomRevenue,
        RoomBlock.TotalRooms as CalculatedRoomTotal,
        RoomBlock.AlternateStartDate,
        RoomBlock.IsAlternateStartDateSelected,
        RoomBlock.TotalRooms,
        RoomBlock.IsSenderCopy,
        BusinessDataInfo.BusinessDataId,
        BusinessDataInfo.BusinessDataTypeId,
        BusinessDataType.Name as BusinessDataType,
        RoomBlock.Budget as RoomBlockBudget
    from  
        RoomBlock with (NOLOCK)
    left join RoomRequirement with (NOLOCK)
    on RoomBlock.RoomBlockId=RoomRequirement.RoomBlockId
    left join RoomAvailability with (NOLOCK)
    on RoomBlock.RoomBlockId=RoomAvailability.RoomBlockId
    left Join BusinessDataInfo with (NOLOCK)
    on RoomBlock.EntityId=BusinessDataInfo.BusinessDataInfoId
    and RoomBlock.EntityTypeId=14
    left join BusinessDataType with (NOLOCK)
    on BusinessDataInfo.BusinessDataTypeId=BusinessDataType.BusinessDataTypeId
    left join EntityType with (NOLOCK)
    on EntityType.EntityTypeId=RoomBlock.EntityTypeId
    left outer join 
    (SELECT RoomBlockId,sum(isnull(RoomAvailability.SingleQuantity,0)*isnull(RoomAvailability.SingleRate,0) 
    + isnull(RoomAvailability.DoubleQuantity,0)*isnull(RoomAvailability.DoubleRate,0) 
    + isnull(RoomAvailability.TripleQuantity,0)*isnull(RoomAvailability.TripleRate,0) 
    + isnull(RoomAvailability.QuadQuantity,0) * isnull(RoomAvailability.QuadRate,0)) as 'RoomRevenue'
    from RoomAvailability group by RoomBlockId) temp
    on temp.roomblockid = RoomBlock.RoomBlockId
 
    GO
    GRANT SELECT ON Reporting.Vw_RoomBlock_ReportingRoomTotals_1 TO PUBLIC
    GO
END

The error is:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'VIEW'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near 'RoomBlockId'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.

If I run the create view outside the If BLock it works.

Thanks
Gary

[brucevde]

The Create View statement must be the first statement in a batch. The code should be

If Exists (Select ...
Drop the View
Go
Create View...
Go

[GaryMazzone]

But I don't want to drop the view if there just create if it is not. Do I have to Drop then Create?

[brucevde]

You could use Exec or sp_ExecuteSQL with a dynamic string to run a separate batch that creates the view.

[techgnome]

Actually, the preferred method is to drop it if it exsists, then re-create it. That allows you to have a script that you can use to update the view in the future (adding or removing a column for instance). The problem is your GOs can't be inside the Begin End block, as it causes the block to terminate. If you want to keep it in the same structure, remove the

GO
    GRANT SELECT ON Reporting.Vw_RoomBlock_ReportingRoomTotals_1 TO PUBLIC
GO

to outside the Begin/end....

-tg

[GaryMazzone]

Ok.... I went with tg suggestion (just much easier) Drop the objects owned by the scheam, then the schema. Cerate the schema and then the views.