View Error

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

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

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

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

If you really want it, then use dynamic sql.

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.

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.

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