create view dbo.v_getdate
as
select dt=CONVERT(char(6),getdate(),12)
go
create function dbo.f_nextBH
returns char(12)
as
begin
declare @dt char(6)
select @dt=dt
from dbo.v_getdate
return(select @dt+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM TO WITH(XLOCK,PAGLOCK)
WHERE BH LIKE @dt+'%'
end
go
---------------------------------------提示如下错误
消息 102,级别 15,状态 1,过程 f_nextBH,第 2 行
'returns' 附近有语法错误。
消息 156,级别 15,状态 1,过程 f_nextBH,第 9 行
关键字 'TO' 附近有语法错误。
as
select dt=CONVERT(char(6),getdate(),12)
go
create function dbo.f_nextBH
returns char(12)
as
begin
declare @dt char(6)
select @dt=dt
from dbo.v_getdate
return(select @dt+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM TO WITH(XLOCK,PAGLOCK)
WHERE BH LIKE @dt+'%'
end
go
---------------------------------------提示如下错误
消息 102,级别 15,状态 1,过程 f_nextBH,第 2 行
'returns' 附近有语法错误。
消息 156,级别 15,状态 1,过程 f_nextBH,第 9 行
关键字 'TO' 附近有语法错误。
---自动编号动态列
if OBJECT_ID('tb') is not null
drop table tbgo
create table tb ( id int ,列 as 'CT000'+ ltrim(序号),序号 int identity )
insert into tb (id ) select 1
union all select 2
go
select id,列 from tb
(2 行受影响)
id 列
----------- -----------------
1 CT0001
2 CT0002(2 行受影响)
returns char(12)
as
begin
declare @dt char(12)
select @dt=dt
from dbo.v_getdate select @dt=@dt+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM [TO] WITH(XLOCK,PAGLOCK)
WHERE [BH] LIKE @dt+'%' return @dt
end
go
create view dbo.v_getdate
as
select dt=CONVERT(char(6),getdate(),12)
go
create function dbo.f_nextBH()
returns char(12)
as
begin
declare @dt char(6)
select @dt=dt
from dbo.v_getdate
return(select @dt+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM [TO] WITH(XLOCK,PAGLOCK)
WHERE BH LIKE @dt+'%')
end
go
returns char(12)
as
begin
declare @dt char(12)
select top 1 @dt=CONVERT(char(6),getdate(),12)
+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM [TO] WITH(XLOCK,PAGLOCK)
WHERE BH LIKE CONVERT(char(6),getdate(),12)+'%' return @dt
end
go
(select @dt+right(1000001+isnull(right(MAX(BH),6),0),6)
FROM TO WITH(XLOCK,PAGLOCK)
WHERE BH LIKE @dt+'%')
比如
insert into t
select *from t1
不如在程序端控制
1. create function 函数名(), 函数名后面有没有参数都要有括号()
2. 在函数里面不能引用物理表
3. 函数中已经返回了(即return) 之后不能再有其它语句