sql server - Can I create a View of Function from a Temp Table -

sql server - Can I create a View of Function from a Temp Table -

can create view or function using below query? uses temp tables. not able create either view or function.

--temp table-- if object_id('tempdb..#enquiries') not null drop table #enquiries go create table #enquiries (id int,pid int,name varchar(50),enquiries int,enquirydate datetime) insert #enquiries select row_number() on (order projectname) row, (select top 1 item dbo.split(e.interestedprojects,',')) projects,p.projectname,count(cast(enquirydate date)) enquiries,cast(enquirydate date) enquirydate tbl_enquiry e inner bring together tbl_projects p on p.projectid = (select top 1 item dbo.split(e.interestedprojects,',')) e.isactive=1 , e.isdeleted=0 , p.isactive=1 , p.isdeleted=0 , p.projectid in (select top 1 item dbo.split(e.interestedprojects,',')) grouping e.interestedprojects,p.projectname,cast(enquirydate date) --sitevisits if object_id('tempdb..#sitevisit') not null drop table #sitevisit go create table #sitevisit (id int,pid int,name varchar(50),sitevisits int,pickupdatetime datetime) insert #sitevisit select row_number() on (order projectname) row,s.projectid,p.projectname name,count(sd.pickupdatetime) sitevisits,cast(pickupdatetime date) pickupdatetime tbl_sitevisit s inner bring together tbl_sitevisitdetails sd on s.sitevisitid=sd.sitevisitid inner bring together tbl_projects p on p.projectid=s.projectid s.isactive=1 , s.isdeleted=0 , sd.isactive=1 , sd.isdeleted=0 grouping s.projectid,sd.pickupdatetime,p.projectname,cast(pickupdatetime date) --bookings if object_id('tempdb..#bookings') not null drop table #bookings go create table #bookings (id int,pid int,name varchar(50),bookings int,bookingdate datetime,revenue money,area float) insert #bookings select row_number() on (order projectname) row, u.projectid projectid,p.projectname,count(u.projectid) bookings,cast(b.bookingdate date) bookingdate,sum(b.totalamount) [revenue],sum(u.unitarea) [area] tbl_bookings b inner bring together tbl_unit u on b.unitid=u.unitid inner bring together tbl_projects p on p.projectid=u.projectid b.isactive=1 , b.isdeleted=0 , u.isactive=1 , u.isdeleted=0 , u.status = 'b' grouping u.projectid,p.projectname,cast(b.bookingdate date),b.totalamount,u.unitarea --order u.projectid if object_id('tempdb..#t1') not null drop table #t1 create table #t1 ( primaryno int, enquirydate date, enquiries int, sitevisits int, bookings int, revenue money, area float, pid int, projectname nvarchar(max) ) insert #t1(primaryno,enquirydate,enquiries,pid,projectname) select id,enquirydate,sum(enquiries) enquiries,pid,name #enquiries grouping id,pid,name,enquirydate declare @svdate date declare @sv11 int declare @bookingdate date declare @bookings11 int declare @revenue11 money declare @area11 float declare @intflag_pw11 int set @intflag_pw11 = 1 declare @tablecntw11 int declare @date date declare cur_sitevisit cursor fast_forward select pickupdatetime #sitevisit open cur_sitevisit fetch next cur_sitevisit @date declare @projectid int declare @count int = 1 while @@fetch_status = 0 begin set @projectid = (select pid #sitevisit id = @count) set @svdate = isnull((select cast(pickupdatetime date) #sitevisit cast(pickupdatetime date) = @date , pid = @projectid grouping pickupdatetime),'-') set @sv11 = isnull((select sitevisits #sitevisit cast(pickupdatetime date) = @date , pid = @projectid grouping sitevisits),0) exec ('update #t1 set sitevisits = ' + @sv11 + ' enquirydate = ' + ''''+ @svdate +''' , pid =' + @projectid) fetch next cur_sitevisit @date set @count = @count + 1 end close cur_sitevisit deallocate cur_sitevisit --for bookings declare @date1 date declare cur_bookings cursor fast_forward select bookingdate #bookings open cur_bookings fetch next cur_bookings @date1 declare @projectid1 int declare @count1 int = 1 while @@fetch_status = 0 begin set @projectid1 = (select pid #bookings id = @count1) set @bookings11 = isnull((select top 1 bookings #bookings cast(bookingdate date) = @date1 , pid = @projectid1 grouping bookings),0) set @bookingdate = isnull((select top 1 cast(bookingdate date) #bookings cast(bookingdate date) = @date1 , pid = @projectid1 grouping cast(bookingdate date)),'-') set @revenue11 = isnull((select top 1 revenue #bookings cast(bookingdate date) = @date1 , pid = @projectid1 grouping revenue),0) set @area11 = isnull((select top 1 area #bookings cast(bookingdate date) = @date1 , pid = @projectid1 grouping area),0) exec ('update #t1 set bookings = ' + @bookings11 + ',revenue=' + @revenue11 + ',area = ' + @area11 + ' enquirydate = ' + '''' + @bookingdate + ''' , pid =' + @projectid1) fetch next cur_bookings @date1 set @count1 = @count1 + 1 end close cur_bookings deallocate cur_bookings select * #t1

you can't create view that, if because you're doing number of inserts , other actions. view created single query.

with regard user-defined functions: cannot utilize dynamic sql, doing:

exec ('update #t1 set bookings = ' + @bookings11 + ',revenue=' + @revenue11 + ',area = ' + @area11 + ' enquirydate = ' + '''' + @bookingdate + ''' , pid =' + @projectid1)

you can't utilize temp tables. both of these limitations documented here: http://msdn.microsoft.com/en-us/library/ms191320.aspx.

but, can of these things in stored procedure. possible direct output of stored procedure temp table or table variable, allowing utilize output in query:

insert dbo.mytable (mytableid, column1, column2) -- arbitrary-chosen column names exec dbo.mystoredprocedure select * dbo.mytable column1 = 'some value'

finally, might able rework sql work table variables rather temp tables, allowed. looked me dynamic sql didn't need dynamic, might able eliminate well.

hope helps.

sql-server sql-server-2008