--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。 --得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END GO--在表中应用函数 CREATE TABLE tb( BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 BEGIN TRAN INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14) COMMIT TRAN--显示结果 SELECT * FROM tb /*--结果 BH col ---------------- ----------- BH000001 1 BH000002 2 BH000003 4 BH000004 14 --*/
如果是这样,不如在存储过程中用动态SQL完成.create procedure my_proc @tbname varchar(20), @colname varchar(20) as begin declare @sql as varchar(100) set @sql = 'select max(' + @colname + ') ' + @colname + ' from ' + @tbname exec(@sql) end goexec my_proc 'authors' , 'au_id'drop procedure my_proc/* au_id ----------- 998-72-3567(所影响的行数为 1 行) */
非常感谢dawugui和pt1314917的回复,问题是这样的,我想通过以下这种方式追加记录: Insert into mytable (id,other_cols) select getnextid(id),'值' from mytablept1314917 的方法有一个问题:就是每个表都要写一个函数。 dawugui的方法是存储过程需要执行一次命令。 再一次感谢你们。
如果你自己写,那还不是用冒泡排序法,执行效率看来比原有的max()和min()速度慢好多;
using System; using System.Text; using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer; using Microsoft.SqlServer.Server;namespace TestFunction { [Serializable()] [SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls=true, IsNullIfEmpty=true, MaxByteSize=8000 ) ] public class CLRMax:IBinarySerialize { string currentValue = string.Empty;
public void Read(System.IO.BinaryReader r) { currentValue = r.ReadString(); } public void Write(System.IO.BinaryWriter w) { w.Write(currentValue); } //Nothing to do public void Init() { } public void Accumulate(SqlString value) { if (value.IsNull) return; if (value.Value.CompareTo(currentValue)>0) currentValue = value.Value; } public void Merge(CLRMax other) { if (this.currentValue.CompareTo(other.currentValue) > 0) this.currentValue = other.currentValue; } public SqlString Terminate() { return new SqlString(currentValue); } } } 编译成Dll後,就可以在sql中使用了。 ex: 编译成CLRMax.dll,放至c:\中。 然後在sql中执行下述语句创建aggregate的function CREATE ASSEMBLY ClrMax FROM 'C:\ClrMax.dll' GO CREATE AGGREGATE ClrMax(@Input NVARCHAR(200)) RETURNS NVARCHAR(MAX) EXTERNAL NAME [ClrMax].[TestFunction.CLRMax] GO开启CLR ENABLED.exec sp_configure 'show advanced options', '1'; go reconfigure; go exec sp_configure 'clr enabled', '1' go reconfigure; exec sp_configure 'show advanced options', '1'; go --使用测试. CREATE TABLE ta (a int,b varchar(10)) GO CREATE TABLE tb (col1 nvarchar(10),col2 decimal(13,4),col3 datetime,col4 bit) GO INSERT INTO ta SELECT 1,'aa' UNION ALL SELECT 2,'aa' UNION ALL SELECT 3,'aa' UNION ALL SELECT 4,'aa' UNION ALL SELECT 10,'bbbbb' UNION ALL SELECT 10,'BBBBB' UNION ALL SELECT 15,'bbbbZ'INSERT INTO tb SELECT 'a',28.4,GETDATE(),0 UNION ALL SELECT 'b',29.4,GETDATE(),NULL UNION ALL SELECT 'c',30.4,DATEADD(DAY,10,GETDATE()),0 GO SELECT b, dbo.ClrMax(a) FROM ta GROUP BY b /* aa 4 bbbbb 10 bbbbZ 15 */SELECT dbo.ClrMax(col1), dbo.ClrMax(col2), dbo.ClrMax(col3), dbo.ClrMax(col4) FROM tb /* c 30.4000 03 20 2009 9:41PM 0 */
真是搞不清楚为什么要这么做?Insert into mytable (id,other_cols) 这一句: select getnextid(id),'值' from mytable 用 select max(id)+1,'值' from mytable 不行吗?
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/
@tbname varchar(20),
@colname varchar(20)
as
begin
declare @sql as varchar(100)
set @sql = 'select max(' + @colname + ') ' + @colname + ' from ' + @tbname
exec(@sql)
end
goexec my_proc 'authors' , 'au_id'drop procedure my_proc/*
au_id
-----------
998-72-3567(所影响的行数为 1 行)
*/
Insert into mytable (id,other_cols) select getnextid(id),'值' from mytablept1314917 的方法有一个问题:就是每个表都要写一个函数。
dawugui的方法是存储过程需要执行一次命令。
再一次感谢你们。
using System;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Server;namespace TestFunction
{
[Serializable()]
[SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToNulls=true,
IsNullIfEmpty=true,
MaxByteSize=8000
)
]
public class CLRMax:IBinarySerialize
{
string currentValue = string.Empty;
public void Read(System.IO.BinaryReader r)
{
currentValue = r.ReadString();
} public void Write(System.IO.BinaryWriter w)
{
w.Write(currentValue);
}
//Nothing to do
public void Init()
{ }
public void Accumulate(SqlString value)
{
if (value.IsNull)
return;
if (value.Value.CompareTo(currentValue)>0)
currentValue = value.Value;
}
public void Merge(CLRMax other)
{
if (this.currentValue.CompareTo(other.currentValue) > 0)
this.currentValue = other.currentValue;
}
public SqlString Terminate()
{
return new SqlString(currentValue);
}
}
}
编译成Dll後,就可以在sql中使用了。
ex:
编译成CLRMax.dll,放至c:\中。
然後在sql中执行下述语句创建aggregate的function
CREATE ASSEMBLY ClrMax FROM 'C:\ClrMax.dll'
GO
CREATE AGGREGATE ClrMax(@Input NVARCHAR(200)) RETURNS NVARCHAR(MAX)
EXTERNAL NAME [ClrMax].[TestFunction.CLRMax]
GO开启CLR ENABLED.exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go --使用测试.
CREATE TABLE ta (a int,b varchar(10))
GO
CREATE TABLE tb (col1 nvarchar(10),col2 decimal(13,4),col3 datetime,col4 bit)
GO
INSERT INTO ta
SELECT 1,'aa'
UNION ALL
SELECT 2,'aa'
UNION ALL
SELECT 3,'aa'
UNION ALL
SELECT 4,'aa'
UNION ALL
SELECT 10,'bbbbb'
UNION ALL
SELECT 10,'BBBBB'
UNION ALL
SELECT 15,'bbbbZ'INSERT INTO tb
SELECT 'a',28.4,GETDATE(),0
UNION ALL
SELECT 'b',29.4,GETDATE(),NULL
UNION ALL
SELECT 'c',30.4,DATEADD(DAY,10,GETDATE()),0
GO
SELECT b, dbo.ClrMax(a) FROM ta GROUP BY b
/*
aa 4
bbbbb 10
bbbbZ 15
*/SELECT dbo.ClrMax(col1),
dbo.ClrMax(col2),
dbo.ClrMax(col3),
dbo.ClrMax(col4)
FROM tb
/*
c 30.4000 03 20 2009 9:41PM 0
*/
真是搞不清楚为什么要这么做?Insert into mytable (id,other_cols)
这一句:
select getnextid(id),'值' from mytable
用 select max(id)+1,'值' from mytable 不行吗?