最近在学习使用存储过程,今天从网上搜到了个生成流水号的函数,于是想在存储过程中调用,但是只要在存储过程中执行这个函数就会出现执行超时的问题,删除调用函数这一条语句就没有问题,大家帮帮忙吧
下面贴出代码,首先是存储过程的set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateOrderlist]AS
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
declare @id nvarchar(12)
set @id = dbo.f_getid()
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), [code=SQL]
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
[/code]下面是函数的set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO--创建得到最大id的函数
ALTER function [dbo].[f_getid]()
returns varchar(12)
as
begin
declare @id varchar(12),@dt varchar(8)
select @dt=dt from v_getdate
select @id=@dt+'-'+right(1001+isnull(right(max(formid),3),0),3)
from orderlist where formid like @dt+'-%'
return(@id)
end
下面贴出代码,首先是存储过程的set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateOrderlist]AS
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
declare @id nvarchar(12)
set @id = dbo.f_getid()
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), [code=SQL]
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
[/code]下面是函数的set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO--创建得到最大id的函数
ALTER function [dbo].[f_getid]()
returns varchar(12)
as
begin
declare @id varchar(12),@dt varchar(8)
select @dt=dt from v_getdate
select @id=@dt+'-'+right(1001+isnull(right(max(formid),3),0),3)
from orderlist where formid like @dt+'-%'
return(@id)
end
go
DBCC OPENTRAN
GO返回什么结果?
SPID (服务器进程 ID): 60
UID (用户 ID): -1
名称 : user_transaction
LSN : (509:2716:3)
开始时间 : 05 10 2010 5:39:17:890PM
SID : 0xfaf0ec867eee874f814eb5b13d25ec68
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
KILL 60
GOEXEC [CreateOrderlist]
GO
这样会超时吗?
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateOrderlist]AS
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
declare @id nvarchar(12)
set @id = dbo.f_getid()
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Raise an error with the
-- details of the exception
IF XACT_STATE()<>0
ROLLBACK TRAN --rollback tran if XACT_STATE>0
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH2).修改orderlist 表上索引CREATE INDEX IX_formid ON orderlist (formid)
GO