CREATE PROCEDURE [sp_UpdateFlow]
(
@II_TYPE varchar(4),
@II_ID varchar(50),
@FlowID varchar(50)
)
as
declare @times varchar(15)
declare @operatorid varchar(50)
declare @sql_Flow nvarchar(500)
declare @sql_Remind nvarchar(500)
declare @sql_ICM nvarchar(500)
SET XACT_ABORT ON
begin transelect @times = (case [step] when 2 then 'CHECK_DATE' when 3 then
'CENSOR_DATE' else 'APPROVE_DATE' end),
@operatorid = (case [step] when 2 then [checker_id] when 3 then
[censor_id] else [approver_id] end)
from Flowtable where uid= @FlowID
set @sql_Flow = N'update [FlowTable] set '+@times+' = getdate(),[step]=[step]+1 where [UID]= '+@FlowID+''
set @sql_Remind = N'update FlowRemind set [OPERATOR_ID]= '+@operatorid+',[step]=[step]+1 WHERE [FLOW_ID]='+@FlowID+''
set @sql_ICM = N'update @II_TYPE set '+@times+' = getdate() where [UID]= '+@II_ID+''
exec sp_executesql @sql_Flow
exec sp_executesql @sql_Remind
exec sp_executesql @sql_ICM
if @@error = 0
begin
commit tran
return
end
else
begin
rollback tran
return
end
GOexecute [sp_UpdateFlow] @II_TYPE='IITF',@II_ID='6134ACAB-D4E9-488F-AADD-44FB6F774493',@FlowID='DAB7A1BB-5C66-4D85-B66D-ED902B8E1CA0'
动态SQL语句,然后放到存储过程里面对3个表进行同时操作,存储过程建立成功,但是执行的时候会报“c66附近有语法错误”,请各位大侠指点指点,不胜感激!
(
@II_TYPE varchar(4),
@II_ID varchar(50),
@FlowID varchar(50)
)
as
declare @times varchar(15)
declare @operatorid varchar(50)
declare @sql_Flow nvarchar(500)
declare @sql_Remind nvarchar(500)
declare @sql_ICM nvarchar(500)
SET XACT_ABORT ON
begin transelect @times = (case [step] when 2 then 'CHECK_DATE' when 3 then
'CENSOR_DATE' else 'APPROVE_DATE' end),
@operatorid = (case [step] when 2 then [checker_id] when 3 then
[censor_id] else [approver_id] end)
from Flowtable where uid= @FlowID
set @sql_Flow = N'update [FlowTable] set '+@times+' = getdate(),[step]=[step]+1 where [UID]= '+@FlowID+''
set @sql_Remind = N'update FlowRemind set [OPERATOR_ID]= '+@operatorid+',[step]=[step]+1 WHERE [FLOW_ID]='+@FlowID+''
set @sql_ICM = N'update @II_TYPE set '+@times+' = getdate() where [UID]= '+@II_ID+''
exec sp_executesql @sql_Flow
exec sp_executesql @sql_Remind
exec sp_executesql @sql_ICM
if @@error = 0
begin
commit tran
return
end
else
begin
rollback tran
return
end
GOexecute [sp_UpdateFlow] @II_TYPE='IITF',@II_ID='6134ACAB-D4E9-488F-AADD-44FB6F774493',@FlowID='DAB7A1BB-5C66-4D85-B66D-ED902B8E1CA0'
动态SQL语句,然后放到存储过程里面对3个表进行同时操作,存储过程建立成功,但是执行的时候会报“c66附近有语法错误”,请各位大侠指点指点,不胜感激!
--从错误上看,应该是字符长度不够。所以被截取了。
declare @sql_Flow nvarchar(500)
declare @sql_Remind nvarchar(500)
declare @sql_ICM nvarchar(500)
这三个变量长度设置大点。试看看``
我按照“libin_ftsafe”的回答修改了存储过程,执行结果如下:
(1 行受影响)(1 行受影响)
消息 1087,级别 15,状态 2,第 1 行
必须声明表变量 "@II_TYPE"。 加大长度,还是报同一错误哦!!
--try:
CREATE PROCEDURE [sp_UpdateFlow]
(
@II_TYPE varchar(4),
@II_ID varchar(50),
@FlowID varchar(50)
)
as
declare @times varchar(15)
declare @operatorid varchar(50)
declare @sql_Flow nvarchar(4000)
declare @sql_Remind nvarchar(4000)
declare @sql_ICM nvarchar(4000)
SET XACT_ABORT ON
begin tran select @times =(case [step] when 2 then 'CHECK_DATE'
when 3 then 'CENSOR_DATE' else 'APPROVE_DATE' end),
@operatorid = (case [step] when 2 then [checker_id]
when 3 then [censor_id] else [approver_id] end)
from Flowtable where uid= @FlowID set @sql_Flow = N'update [FlowTable] set '+@times+' = getdate(),[step]=[step]+1 where [UID]= '''+@FlowID+''''
set @sql_Remind = N'update FlowRemind set [OPERATOR_ID]= '+@operatorid+',[step]=[step]+1 WHERE [FLOW_ID]='''+@FlowID+''''
set @sql_ICM = N'update @II_TYPE set '+@times+' = getdate() where [UID]= '''+@II_ID+''''
exec sp_executesql @sql_Flow
exec sp_executesql @sql_Remind
exec sp_executesql @sql_ICM
if @@error = 0
begin
commit tran
return
end
else
begin
rollback tran
return
end
GO
--还有个地方,改漏了。用这个:
CREATE PROCEDURE [sp_UpdateFlow]
(
@II_TYPE varchar(4),
@II_ID varchar(50),
@FlowID varchar(50)
)
as
declare @times varchar(15)
declare @operatorid varchar(50)
declare @sql_Flow nvarchar(4000)
declare @sql_Remind nvarchar(4000)
declare @sql_ICM nvarchar(4000)
SET XACT_ABORT ON
begin tran select @times =(case [step] when 2 then 'CHECK_DATE'
when 3 then 'CENSOR_DATE' else 'APPROVE_DATE' end),
@operatorid = (case [step] when 2 then [checker_id]
when 3 then [censor_id] else [approver_id] end)
from Flowtable where uid= @FlowID set @sql_Flow = N'update [FlowTable] set '+@times+' = getdate(),[step]=[step]+1 where [UID]= '''+@FlowID+''''
set @sql_Remind = N'update FlowRemind set [OPERATOR_ID]= '+@operatorid+',[step]=[step]+1 WHERE [FLOW_ID]='''+@FlowID+''''
set @sql_ICM = N'update '+@II_TYPE+' set '+@times+' = getdate() where [UID]= '''+@II_ID+''''
exec sp_executesql @sql_Flow
exec sp_executesql @sql_Remind
exec sp_executesql @sql_ICM
if @@error = 0
begin
commit tran
return
end
else
begin
rollback tran
return
end
GO
set@sql_ICM= N'update @II_TYPE set '+@times+' = getdate() where [UID]= '''+@II_ID+''''
改成了
set@sql_ICM= N'update '''+@II_TYPE+'''' set '+@times+' = getdate() where [UID]= '''+@II_ID+''''
再执行,结果如下:
(1 行受影响)(1 行受影响)
消息 102,级别 15,状态 1,第 1 行
'IITF' 附近有语法错误。