许多表中都需要获取新记录的主键值,每次都需要连接数据库并计算,
所以写了个能适应不同表的存储过程,以及可定义主键前缀及默认长度
SET
QUOTED_IDENTIFIER
ON
GO
SET ANSI_NULLS ON
GO
/*
获得表中新的主键值
调用方法:
DECLARE @NewId VARCHAR(20)
EXEC GetNewID 'Customer','CustId','C', 4, @NewId OUT
SELECT @NewId
输入参数:
@TableName 表名
@KeyName 主键名
@Prefix 主键前缀
@Length 表中无数据时的默认长度
输出参数:
@NewId 返回的新主键值
*/
ALTER PROCEDURE GetNewID
(
@TableName NVARCHAR ( 100 ),
@KeyName NVARCHAR ( 100 ),
@Prefix NVARCHAR ( 10 ),
@Length INT ,
@NewId NVARCHAR ( 20 ) OUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxId VARCHAR ( 20 )
DECLARE @Fill NVARCHAR ( 20 )
-- DECLARE @NewId VARCHAR(20)
DECLARE @SQL NVARCHAR ( 1000 )
IF (( @Prefix IS NULL ))
SET @Prefix = ''
SET @Fill = ' 00000000000000000000 '
SET @SQL = N ' SELECT @MaxId = ISNULL(RTRIM(LTRIM(MAX( ' + @KeyName + ' ))), ''' + LEFT ( @Prefix + @Fill , @Length ) + ''' ) FROM [ ' + @TableName + ' ] '
EXEC sp_executesql @SQL ,N ' @MaxId VARCHAR(20) OUT ' , @MaxId OUT
-- PRINT 'OUT:'+@MaxId
SET @NewId = @Prefix + RIGHT ( @Fill + CAST ( ( RIGHT ( @MaxId , LEN ( @MaxId ) - LEN ( @Prefix )) + 1 ) AS NVARCHAR ), LEN ( @MaxId ) - LEN ( @Prefix ))
-- SELECT @NewId
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
/*
获得表中新的主键值
调用方法:
DECLARE @NewId VARCHAR(20)
EXEC GetNewID 'Customer','CustId','C', 4, @NewId OUT
SELECT @NewId
输入参数:
@TableName 表名
@KeyName 主键名
@Prefix 主键前缀
@Length 表中无数据时的默认长度
输出参数:
@NewId 返回的新主键值
*/
ALTER PROCEDURE GetNewID
(
@TableName NVARCHAR ( 100 ),
@KeyName NVARCHAR ( 100 ),
@Prefix NVARCHAR ( 10 ),
@Length INT ,
@NewId NVARCHAR ( 20 ) OUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxId VARCHAR ( 20 )
DECLARE @Fill NVARCHAR ( 20 )
-- DECLARE @NewId VARCHAR(20)
DECLARE @SQL NVARCHAR ( 1000 )
IF (( @Prefix IS NULL ))
SET @Prefix = ''
SET @Fill = ' 00000000000000000000 '
SET @SQL = N ' SELECT @MaxId = ISNULL(RTRIM(LTRIM(MAX( ' + @KeyName + ' ))), ''' + LEFT ( @Prefix + @Fill , @Length ) + ''' ) FROM [ ' + @TableName + ' ] '
EXEC sp_executesql @SQL ,N ' @MaxId VARCHAR(20) OUT ' , @MaxId OUT
-- PRINT 'OUT:'+@MaxId
SET @NewId = @Prefix + RIGHT ( @Fill + CAST ( ( RIGHT ( @MaxId , LEN ( @MaxId ) - LEN ( @Prefix )) + 1 ) AS NVARCHAR ), LEN ( @MaxId ) - LEN ( @Prefix ))
-- SELECT @NewId
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
文章来源: http://www.cnblogs.com/Dove/archive/2006/11/06/552035.html