22,210
社区成员
发帖
与我相关
我的任务
分享
真是搞不清楚为什么要这么做?
Insert into mytable (id,other_cols)
这一句:
select getnextid(id),'值' from mytable
用 select max(id)+1,'值' from mytable 不行吗?
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);
}
}
}
CREATE ASSEMBLY ClrMax FROM 'C:\ClrMax.dll'
GO
CREATE AGGREGATE ClrMax(@Input NVARCHAR(200)) RETURNS NVARCHAR(MAX)
EXTERNAL NAME [ClrMax].[TestFunction.CLRMax]
GO
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
*/
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
go
exec my_proc 'authors' , 'au_id'
drop procedure my_proc
/*
au_id
-----------
998-72-3567
(所影响的行数为 1 行)
*/
--下面的代码生成长度为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
--*/