大家帮我看一下这个存储过程那里错了。老是提示：
消息 154，级别 15，状态 1，过程 Update_2，第 24 行
a USE database statement is not allowed in a procedure, function or trigger.
这个错误位置就是：USE PAOS_TEMP
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE Update_2
@P_date char(10),
@P_user char(10)AS--BEGINSET NOCOUNT ON;
USE PAOS_TEMP
Go--写日志
declare @ins_str varchar(300)
set @ins_str='insert into dbo.ins_log (P_name,Beg_time,state) values (''PAOS_Trim_regs'',CONVERT(varchar,getdate(),120),0)'
exec(@ins_str)declare starde cursor-------------------------------------定义游标“starde”
for Select updateid From dbo.[update] where updatename='PAOS_trim_regs'---
-----------------------------取出上一次成功同步的数据------------
use pacifcandes503
Godeclare @ST int,@id int,@prperiod int,@shift int,@prday datetime,@employee int,@material int,@weight real,@regtime datetime,@rtype smallint,
@maxid int,@count int,@check_count int,@check_maxid int,@maxupdate int,@ii int,
@Ins_row int
----->>update表中上一次成功同步的id------@ST
----->>设置最大同步数值------@maxupdate
----->>取出最新数据.游标-----R_x cursor
----->>源表预期同步数量------@count
----->>源表预期同步最大ID值--@maxid
----->>update表中maxid-------@check_maxid
----->>ST+maxupdate本次要同步的数量----@ii
Set @Ins_row=0
Set @maxupdate=100
-----------------------------
open starde
fetch next from starde into @ST
DEALLOCATE stardeset @ii=@ST+@maxupdate -----<<预期最大值同步值
--set @ii=41000367
declare R_x cursorfor select max(id),count(id) from dbo.trim_regs as timer where id >@ST and id<=@ii
-----<<须更改表名
open R_x
fetch next from R_x into @maxid,@count
DEALLOCATE R_x
------预期同步最大值-----
print '--------------'
print 'ST='+rtrim(@ST) -----------上一次更新最大ID
print 'ST+maxupdate='+rtrim(@ii)----本次预期更新到最大ID
print 'maxupdate='+rtrim(@maxupdate)---预设每次更新纪录数
print 'MAX id='+ISNULL(rtrim(@maxid),'-')-----本次预期更新到最大ID
print 'count='+rtrim(@count) -----本次预计更新行数
print '--------------'
-----取出最新数据----
declare R_x cursor
for select id,prperiod,shift,prday,employee,material,weight,regtime,rtype from dbo.trim_regs as regs where id>@ST and id<=@ii Order by id
open R_x
fetch next from R_x into @id,@prperiod,@shift,@prday,@employee,@material,@weight,@regtime,@rtype
declare @str varchar(300)
while @@fetch_status=0
begin
set @str='Insert into dbo.PAOS_trim_regs (id,prperiod,shift,prday,employee,material,weight,regtime,rtype,state) Values('+rtrim(@id)+','+rtrim(@prperiod)+','+char(39)+rtrim(@shift)+char(39)+','+char(39)+rtrim(@prday)+char(39)+','+char(39)+ltrim(@employee)+char(39)+','+rtrim(@material)+','+rtrim(@weight)+','+char(39)+rtrim(@regtime)+char(39)+','+rtrim(@rtype)+',0)'
---,'+char(39)+ISNULL(rtrim(@aseconds),'NULL')+char(39)+' ---为空的设置
USE PAOS_TEMP
exec(@str)-----------------写入OA数据库
Set @Ins_row=@Ins_row+1 ------计数器，每插入一条，加1
-----------------纪录更新id值---------------
declare check_a cursor
for select max(id) from dbo.PAOS_trim_regs -----<<须更改表名
open check_a
fetch next from check_a into @check_maxid
DEALLOCATE check_a
begin
set @str='Update dbo.[update] Set updateid='+char(39)+rtrim(@check_maxid)+char(39)+' where updatename='+char(39)+'PAOS_trim_regs'+char(39)
exec(@str)
end
-------------------------------------------
use pacifcandes503
fetch next from R_x into @id,@prperiod,@shift,@prday,@employee,@material,@weight,@regtime,@rtype
end
DEALLOCATE R_x
-----------------------------校验数据量------------------
print'----------------------'
print 'ST='+rtrim(@ST)
print 'Maxid='+rtrim(@maxid)
print 'Check_maxid='+rtrim(@check_maxid)
print 'Count='+rtrim(@count)
print 'Check_count='+rtrim(@check_count)
print'-----------------------'
---------------------------清理已同步数据----------------
USE PAOS_TEMP
set @str='Delete From dbo.PAOS_trim_regs where state=1' -----<<须更改表名
exec(@str)
---写更新时间
set @str='Update dbo.[update] Set end_time=CONVERT(varchar,getdate(),120) where updatename='+char(39)+'PAOS_trim_regs'+char(39)
exec(@str)
--写日志
SET @str='update dbo.ins_log set end_time=CONVERT(varchar,getdate(),120),ins_row='+rtrim(@Ins_row)+',state=1 where state=0 and P_name=''PAOS_trim_regs'''
exec(@str)--end
GO
消息 154，级别 15，状态 1，过程 Update_2，第 24 行
a USE database statement is not allowed in a procedure, function or trigger.
这个错误位置就是：USE PAOS_TEMP
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE Update_2
@P_date char(10),
@P_user char(10)AS--BEGINSET NOCOUNT ON;
USE PAOS_TEMP
Go--写日志
declare @ins_str varchar(300)
set @ins_str='insert into dbo.ins_log (P_name,Beg_time,state) values (''PAOS_Trim_regs'',CONVERT(varchar,getdate(),120),0)'
exec(@ins_str)declare starde cursor-------------------------------------定义游标“starde”
for Select updateid From dbo.[update] where updatename='PAOS_trim_regs'---
-----------------------------取出上一次成功同步的数据------------
use pacifcandes503
Godeclare @ST int,@id int,@prperiod int,@shift int,@prday datetime,@employee int,@material int,@weight real,@regtime datetime,@rtype smallint,
@maxid int,@count int,@check_count int,@check_maxid int,@maxupdate int,@ii int,
@Ins_row int
----->>update表中上一次成功同步的id------@ST
----->>设置最大同步数值------@maxupdate
----->>取出最新数据.游标-----R_x cursor
----->>源表预期同步数量------@count
----->>源表预期同步最大ID值--@maxid
----->>update表中maxid-------@check_maxid
----->>ST+maxupdate本次要同步的数量----@ii
Set @Ins_row=0
Set @maxupdate=100
-----------------------------
open starde
fetch next from starde into @ST
DEALLOCATE stardeset @ii=@ST+@maxupdate -----<<预期最大值同步值
--set @ii=41000367
declare R_x cursorfor select max(id),count(id) from dbo.trim_regs as timer where id >@ST and id<=@ii
-----<<须更改表名
open R_x
fetch next from R_x into @maxid,@count
DEALLOCATE R_x
------预期同步最大值-----
print '--------------'
print 'ST='+rtrim(@ST) -----------上一次更新最大ID
print 'ST+maxupdate='+rtrim(@ii)----本次预期更新到最大ID
print 'maxupdate='+rtrim(@maxupdate)---预设每次更新纪录数
print 'MAX id='+ISNULL(rtrim(@maxid),'-')-----本次预期更新到最大ID
print 'count='+rtrim(@count) -----本次预计更新行数
print '--------------'
-----取出最新数据----
declare R_x cursor
for select id,prperiod,shift,prday,employee,material,weight,regtime,rtype from dbo.trim_regs as regs where id>@ST and id<=@ii Order by id
open R_x
fetch next from R_x into @id,@prperiod,@shift,@prday,@employee,@material,@weight,@regtime,@rtype
declare @str varchar(300)
while @@fetch_status=0
begin
set @str='Insert into dbo.PAOS_trim_regs (id,prperiod,shift,prday,employee,material,weight,regtime,rtype,state) Values('+rtrim(@id)+','+rtrim(@prperiod)+','+char(39)+rtrim(@shift)+char(39)+','+char(39)+rtrim(@prday)+char(39)+','+char(39)+ltrim(@employee)+char(39)+','+rtrim(@material)+','+rtrim(@weight)+','+char(39)+rtrim(@regtime)+char(39)+','+rtrim(@rtype)+',0)'
---,'+char(39)+ISNULL(rtrim(@aseconds),'NULL')+char(39)+' ---为空的设置
USE PAOS_TEMP
exec(@str)-----------------写入OA数据库
Set @Ins_row=@Ins_row+1 ------计数器，每插入一条，加1
-----------------纪录更新id值---------------
declare check_a cursor
for select max(id) from dbo.PAOS_trim_regs -----<<须更改表名
open check_a
fetch next from check_a into @check_maxid
DEALLOCATE check_a
begin
set @str='Update dbo.[update] Set updateid='+char(39)+rtrim(@check_maxid)+char(39)+' where updatename='+char(39)+'PAOS_trim_regs'+char(39)
exec(@str)
end
-------------------------------------------
use pacifcandes503
fetch next from R_x into @id,@prperiod,@shift,@prday,@employee,@material,@weight,@regtime,@rtype
end
DEALLOCATE R_x
-----------------------------校验数据量------------------
print'----------------------'
print 'ST='+rtrim(@ST)
print 'Maxid='+rtrim(@maxid)
print 'Check_maxid='+rtrim(@check_maxid)
print 'Count='+rtrim(@count)
print 'Check_count='+rtrim(@check_count)
print'-----------------------'
---------------------------清理已同步数据----------------
USE PAOS_TEMP
set @str='Delete From dbo.PAOS_trim_regs where state=1' -----<<须更改表名
exec(@str)
---写更新时间
set @str='Update dbo.[update] Set end_time=CONVERT(varchar,getdate(),120) where updatename='+char(39)+'PAOS_trim_regs'+char(39)
exec(@str)
--写日志
SET @str='update dbo.ins_log set end_time=CONVERT(varchar,getdate(),120),ins_row='+rtrim(@Ins_row)+',state=1 where state=0 and P_name=''PAOS_trim_regs'''
exec(@str)--end
GO
Go
去掉
如果需要使用这个库里的表，可以这么写select * from PAOS_TEMP.dbo.表名
SET NOCOUNT ON; 这个建议放在create proc 上面执行
USE PAOS_TEMP 这个在proc 中也不要用，可以通过PAOS_TEMP..tb 来确定表名
Go
用PAOS_TEMP.dbo.tbname