你的根本不是临时表,多个用户操作时当然会出问题.看你的存储过程,你生成的表在存储过程后边还要调用到,是吧?
那就改用全局临时表:CREATE PROCEDURE dbo.Gen_T_EmployeePayrollDetail
@tbname varchar(250) output --加一个输出参数,返回此存储生成的全局临时表的表名,以方便其他过程调用
AS
declare @sql varchar(8000)
set @tbname='[T_EmployeePayrollDetail_'+cast(newid() as varchar(40))+']' --生成一个临时表名
set @sql='select employee_code'
select @sql=@sql+',sum(case when payroll_item_uuid='''+payroll_item_uuid+''' then amount end) ['+payroll_item_uuid+']'
from (select distinct payroll_item_uuid from employee_payroll_item_detail) a
set @sql=@sql+' into '+@tbname+' from employee_payroll_item_detail group by employee_code'
exec (@sql)
GO
那就改用全局临时表:CREATE PROCEDURE dbo.Gen_T_EmployeePayrollDetail
@tbname varchar(250) output --加一个输出参数,返回此存储生成的全局临时表的表名,以方便其他过程调用
AS
declare @sql varchar(8000)
set @tbname='[T_EmployeePayrollDetail_'+cast(newid() as varchar(40))+']' --生成一个临时表名
set @sql='select employee_code'
select @sql=@sql+',sum(case when payroll_item_uuid='''+payroll_item_uuid+''' then amount end) ['+payroll_item_uuid+']'
from (select distinct payroll_item_uuid from employee_payroll_item_detail) a
set @sql=@sql+' into '+@tbname+' from employee_payroll_item_detail group by employee_code'
exec (@sql)
GO
解决方案 »
- 存储过程怎么理解啊
- 15万条数据的表,select和update引发的死锁问题
- 返回相差的时间:
- win98下用必须用MSDE的实际服务器名字,如“王丹可\YH”而不能用等同的"127.0.0.1\YH"或"localhost\yh"代替,是什么原因?而实际的数据库名“王丹可\YH”又如何直接得到?
- 新手,求一简单的触发器,谢谢大家
- 日期问题,有点难度
- SQL语句的一个问题
- 求助!数据报表的一个实现问题
- 如何打包制作含有SQL server(个人版)数据库的应用软件的安装程序?急用!!
- 求工资变动情况表sql语句写法
- 关于Tran-SQL语句的问题:如何获取库中第21至第40条记录?
- 请教,两个查询结果的合并方法!
declare @tbname varchar(250)
exec Gen_T_EmployeePayrollDetail ,@tbname outputprint '已经生成临时表:'+@tbname--处理完成后删除全局临时表.
exec('drop table '+@tbname)
我的存储过程如下:CREATE PROCEDURE dbo.prGen_T_EmployeePayrollDetail
@tbname varchar(250) output
AS
declare @sql varchar(8000)
set @tbname='[T_EmployeePayrollDetail_'+cast(newid() as varchar(40))+']'
set @sql='select employee_uuid, employee_code, official_name, business_unit, department, year, period '
select @sql=@sql+',sum(case when payroll_item_uuid='''+payroll_item_uuid+''' then amount end) ['+payroll_item_uuid+']'
from (select distinct payroll_item_uuid from employee_payroll_item_detail) a
set @sql=@sql+' into '+@tbname+' from employee_payroll_item_detail where flag_payroll=1 and flag_person=1 group by employee_uuid, employee_code, official_name, business_unit, department, year, period '
exec (@sql)
GO调用的VB代码如下Private Sub Gen_T_EmployeePayrollDetail(ByRef TableName As String)
Dim strSQL As String
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param As New ADODB.Parameter
With cmd
.ActiveConnection = gobjConnection
.CommandText = "prGen_T_EmployeePayrollDetail"
.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("Output", adVarChar, adParamOutput, , 0)
.Parameters.Append param
End With
Set rst = cmd.Execute
TableName = cmd(0)
Set cmd = Nothing
rst.Close
Set rst = Nothing
End Sub参数的定义哪里有错呢?
你怎么又对传进去的参数付值?
内存表需要定义啊
create table #tablename ....................
然后在select * into #tablename....
最后是返回rs用的为select * from #tablename
删除表drop #tablename
我试过如下语句:Set param = cmd.CreateParameter("Output", adVarChar, adParamOutput)发生同样错误。