问题描述

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

ado.net执行存储过程提示找不到该临时表，但是在sql中直接执行就没问题，求教各位大神。
sql所在环境windows azure

  if (exists (select * from sys.objects where name = 'proc_Activity_StudioList'))    drop proc proc_Activity_StudioListgocreate 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 floatPId intUserNickName nvarchar(500)UserAvatar nvarchar(500)UpCount intCommentCount intRegisterCount int    Userid  bigintRegion 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.UpCounta.CommentCounta.RegisterCounttemp.[Userid]r.[CityName] as Regionr1.[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 @sqlexec( @sql)IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'#StudioList') AND OBJECTPROPERTY(ID 'IsTable') = 1)         drop table #StudioList  goexec 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"":0type"":1collegeId"":0index"":1size"":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_StudioListgocreate 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 floatPId intUserNickName nvarchar(500)UserAvatar nvarchar(500)UpCount intCommentCount intRegisterCount int    Userid  bigintRegion 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.UpCounta.CommentCounta.RegisterCounttemp.[Userid]r.[CityName] as Regionr1.[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 @sqlexec( @sql)--局部临时表生命周期仅限于这个存储过程的执行过程中，执行完自动drop了--IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'#StudioList') AND OBJECTPROPERTY(ID 'IsTable') = 1) --        drop table #StudioList  goexec proc_Activity_StudioList @province=0@type=1@collegeId=0@index=1@size=30