Create view from cursor

I have multiple locations that I want to create views for each
individual location.

I am using a cursor to create the views for each location. So, the
cursor grabs site #1 then <should> create view_site_#1, then grab site
#2 and <should> create view_site_#2.

For some reason it doesn't like the view name with the @.site in it.
Any ideas of how to get this done?

Here's the cursor...

declare @.site varchar(5)

declare c_site cursor for
select station from VHAISLCAUDIA.VISN_SITE
order by station

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

CREATE VIEW Site_All_Data_+ @.site
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE (Site = @.site)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site
return

endThis is actually one of the few times that a cursor and dynamic SQL can
be useful; this administrative scripting is a great target for this
sort of stuff.

Anyway, you need to use dynamic SQL for this:

DECLARE @.tSite TABLE (site varchar(5))
INSERT INTO @.tSite
SELECT 'ABCDE'
UNION ALL
SELECT 'FGHIJ'

declare @.site varchar(5)
DECLARE @.SQL nvarchar(2000)

declare c_site cursor for
select site from @.tsite

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

SET @.SQL = 'CREATE VIEW Site_All_Data_' + @.site + '
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE Site = ''' + @.site + ''''

exec (@.SQL)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site

HTH,
Stu|||Worked like a charm!

Thanks for helping a developer that forgets the 'simple' stuff
sometimes.

db55