July 30, 2008 at 3:52 am
Hi people
Just a question regarding views..
Can i use/reference a view in a stored procedure that uses a temp table to generate a result.
basically, i want create a view that shreds XML then reference it in my stored procedure to join onto other tables. Im hoping that this will optimize my stored proc.
Thanks
Yusuf
July 30, 2008 at 5:55 am
You can use views, join them to temp tables, and put it SP's.
But you can't create or reference a temp table within a view.
You have to do the joining, etc, in the SP.
July 30, 2008 at 5:59 am
thanks
July 30, 2008 at 7:26 am
views can't reference temp tables, but they can reference synonyms which in turn can reference temp tables. run the code below in a scratch db.
select getdate() as date, 1 as num into #z
go
create view MyView as
select A.* from #z as A cross join #z as B
-- this fails
go
create synonym MyTable for #z
go
create view MyView as
select A.* from MyTable A cross join MyTable as B
-- this succeeds
go
create proc MyProc
as
begin
select * from MyTable
end
go
exec MyProc
-- date, 1
go
drop table #z
select getdate() as date, 2 as num into #z
go
exec MyProc
-- date, 2
go
drop table #z
select 'red' as color, getdate() as date, 3 as num into #z
go
exec MyProc
-- 'red', date (num is not selected)
go
drop procedure MyProc
drop view MyView
drop synonym MyTable
drop table #z
go
Note that in the 3rd call to MyProc, the results have changed type. This is because the view was initially created when MyTable (#z) only had 2 columns in it. So, the view will always return columns 1 and 2 of MyTable (#z).
Using synonyms forces late binding, so this probably won't 'optimize' your proc, but it may make your task easier since you'll be able to tweak the view's joins outside of the proc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply