Create view from cursor

[db55]

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.VI SN_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_Suggest ed_Data]
WHERE (Site = @site)


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

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

end

[Stu]

Re: Create view from cursor

This 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_Suggest ed_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

[db55]

Re: Create view from cursor

Worked like a charm!

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

db55