By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,463 Members | 3,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,463 IT Pros & Developers. It's quick & easy.

Create view from cursor

P: n/a
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

end

May 12 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Stu
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_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

May 12 '06 #2

P: n/a
Worked like a charm!

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

db55

May 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.