ado.net执行存储过程提示找不到该临时表,但是在sql中直接执行就没问题,求教各位大神。
sql所在环境windows azure
if (exists (select * from sys.objects where name = 'proc_Activity_StudioList'))
drop proc proc_Activity_StudioList
go
create proc proc_Activity_StudioList
@province int,
@type int,
@collegeId int,
@index int,
@size int
as
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'#StudioList') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
drop table #StudioList
declare @page int,@pageend int, @sql nvarchar(4000)
set @page =(@index - 1) * @size + 1
set @pageend = @index*@size
set @sql = 'create table #StudioList(num float,PId int,UserNickName nvarchar(500),UserAvatar nvarchar(500),UpCount int,CommentCount int,RegisterCount int,
Userid bigint,Region nvarchar(100),Province nvarchar(100))
INSERT INTO #StudioList
select distinct num=( a.[RegisterCount]*0.5 + a.[UpCount]*0.25 + a.[CommentCount]*0.25 ), a.[PId],temp.[UserNickName],temp.[UserAvatar],a.UpCount,a.CommentCount,a.RegisterCount,temp.[Userid],r.[CityName] as Region,r1.[CityName] as Province from [UserInfo] as temp
inner join [dbo].ActivityStudioSet as a on a.userid = temp.[UserId]
left join [dbo].[Region] as r on temp.[Region] = r.[Codeid]
left join [dbo].[Region] as r1 on temp.[Province] = r1.[Codeid]
where temp.[UserRole]=3 '
if(@province > 0)
set @sql = @sql +' and temp.[Province] = '+cast(@province as varchar(10))
if(@collegeId > 0)
set @sql = @sql +' and temp.CollegeId = '+cast(@collegeId as varchar(10))
if(@type=1)
set @sql = @sql +' and a.[IsRecommend] =1 '
--print @sql
exec(@sql)
set @sql ='select * from ( select ROW_NUMBER() over(order by [Userid] ) as rownumber, * from #StudioList ) as a'
if(@type = 1)
set @sql = @sql + ' where a.rowNumber between '+cast(@page as varchar(10))+' and '+cast(@pageend as varchar(10))
else if(@type = 2)
set @sql = @sql + ' where a.rowNumber between '+cast(@page as varchar(10))+' and '+cast(@pageend as varchar(10))+' order by a.[RegisterCount] desc '
else
set @sql = @sql + ' where a.rowNumber between '+cast(@page as varchar(10))+' and '+cast(@pageend as varchar(10))+' order by num desc '
--print @sql
exec( @sql)
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'#StudioList') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
drop table #StudioList
go
exec proc_Activity_StudioList @province=0,@type=1,@collegeId=0,@index=1,@size=30