在SQL Server中动态自动生成编号 [英] Dynamic auto generate number in sql server

问题描述

你好

我想要sql server中的过程或函数，通过该过程或函数，我将发送表名和表的一个字段(其为递增数)，并且将返回该字段的最大数量，该字段将被放入变量中并返回最大数量.




谢谢....

Hello

I want a procedure or function in sql server by which i will send table name and one field of the table which is an increment number and it will return me max number of that field which i will put in a variable and return the max number.




Thanks....

推荐答案

像这样的事情:

Something like this:

DECLARE @TableName NVARCHAR(100)
DECLARE @FieldName NVARCHAR(100)

DECLARE @Query NVARCHAR(MAX)

SET @Query = 'SELECT MAX(' + @FieldName + ') FROM ' + @TableName
EXEC(@Query)

当您将数据插入表中后，服务器可以为您生成该值-请勿自己尝试，因为您可能会遇到并发问题(两个用户使用相同的新id值添加数据).
在表定义中，使用启用了"identity"属性的整数("Is Identity"，"Yes").
当您需要在插入数据库后知道该clent上的值时，请执行SELECT @@ID.

When you insert data into a table, the server can generate that value for you - do not try it yourself, as you might face concurrency problems (two users adding data with the same new id value).
I the table definition, use an integer with the "identity" property switched on ("Is Identity", "Yes").
When you need to know that value on your clent after inserting into the database, do a SELECT @@ID.

在Query下方使用，

use below Query,

select isnull(max(AutoIncrFldNm),0) + 1 from tblnm

在存储过程中，如果要传递表名&动态字段名
然后，

In store-procedure if you are passing tablename & fieldname dynamically
then,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sp_ReturnNextAutoId
	@tblNm as varchar(50),
    @fldNm as varchar(50)
AS
BEGIN
	DECLARE @execquery AS NVARCHAR(MAX)
	SET @execquery = 'select isnull(max(' + QUOTENAME(@fldNm) + '),0) + 1 from ' + QUOTENAME(@tblNm)
	execute(@execquery)
END
GO

--tocheck sp
--exec Sp_ReturnNextAutoId 'tbl1','fld1'

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于在SQL Server中动态自动生成编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！