ado.net 执行存储过程中使用临时表出错

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

Exception=Metis.MobileServer.MetisException.Exceptions.MetisDbException: Type:Error
Description: SQL 执行异常!
InnerException: Type: System.Data.SqlClient.SqlException (InnerException 1)
Message: Invalid object name '#StudioList'.
Source: .Net SqlClient Data Provider
HelpLink:
Data: System.Collections.ListDictionaryInternal
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Dapper.SqlMapper.<QueryImpl>d__111.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToListTSource
at Dapper.SqlMapper.QueryT
at Metis.MobileServer.DataAccess.BaseDal.QueryListByProcT
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])

Message: QueryListByProc :User=>proc_Activity_StudioList=>{"province":0,"type":1,"collegeId":0,"index":1,"size":30}

你用的是azure sql还是azure虚拟机中的sql server？

你好 Shrap2011，

你的问题可能是因为局部临时表（以#开头）的作用域问题引起的，修改了一下你的存储过程的代码，应该没问题了。

--if (exists (select * from sys.objects where name = 'proc_Activity_StudioList'))
if object_id('proc_Activity_StudioList','P') is not null
    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) 
 IF object_id(N'tempdb..#StudioList','U') IS NOT NULL
        drop table #StudioList  

  declare @page int,@pageend int, @sql nvarchar(4000)
    set @page =(@index - 1) * @size + 1
    set @pageend = @index*@size

    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))

    SELECT * FROM #StudioList

      set @sql = '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)
--局部临时表生命周期仅限于这个存储过程的执行过程中，执行完自动drop了
--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