sqlserver 自定义函数-2023修改整理

千里之行，始于足下让知识带有温度。第第2页/共2页精品文档推荐sqlserver自定义函数--计算当前月的实际天数

CreateFUNCTIONdbo.CalcDaysOfMonth(@timevarchar(6))

RETURNSint

AS

BEGIN

DECLARE@Daysint

DECLARE@Monthint

DECLARE@Yearint

SET@Year=SUBSTRING(@time,1,4)

SET@Month=SUBSTRING(@time,5,6)

if(

@Month='1'

OR@Month='3'

OR@Month='5'

OR@Month='7'

OR@Month='8'

OR@Month='10'

OR@Month='12'

)

set@Days=31

elseif(

@Month='4'

OR@Month='6'

OR@Month='9'

OR@Month='11'

)

set@Days=30;

else

if(@Year%400=0OR(@Year%4=0AND@Year%1000))

set@Days=29

else

set@Days=28

RETURN(@Days)

END

--确定某年某月有多少天

CreateFUNCTIONDaysInMonth(@datedatetime)Returnsint

AS

BEGIN

RETURNDay(dateadd(mi,-3,DATEADD(m,DATEDIFF(m,0,@date)+1,0)))

END

--哪一天是输入时间的星期一

CreateFUNCTIONMondayInDate(@datedatetime)RETURNSDATETIME

AS

BEGIN

RETURNDATEADD(week,DATEDIFF(week,0,@date),0)

END

--输入时间的季度的第一天

CreateFUNCTIONQuarterInDate(@datedatetime)RETURNSDATETIME

AS

BEGIN

RETURNDATEADD(quarter,DATEDIFF(quarter,0,@date),0)

END

--输入时间的季度的天数

CreateFUNCTIONQuarterDaysInDate(@datedatetime)RETURNSINT

AS

BEGIN

declare@mtinyint,@timeSMALLDATETIME

select@m=month(@date)

select@m=casewhen@mbetween1and3then1

when@mbetween4and6then4

when@mbetween7and9then7

else10end

select@time=datename(year,@date)+'-'+convert(varchar(10),@m)+'-01'

returndatediff(day,@time,dateadd(mm,3,@time))

END

--按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。

CreatefunctionGet_StrArrayLength

(

@strvarchar(1024),--要分割的字符串

@splitvarchar(10)--分隔符号

)

returnsint

as

begin

declare@locationint

declare@startint

declare@lengthint

set@str=ltrim(rtrim(@str))

set@location=charindex(@split,@str)

set@length=1

while@location0

begin

set@start=@location+1

set@location=charindex(@split,@str,@start)

set@length=@length+1

end

return@length

END

--按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样便利

CreatefunctionGet_StrArrayStrOfIndex

(

@strvarchar(1024),--要分割的字符串

@splitvarchar(10),--分隔符号

@indexint--取第几个元素

)

returnsvarchar(1024)

as

begin

declare@locationint

declare@startint

declare@nextint

declare@seedint

set@str=ltrim(rtrim(@str))

set@start=1

set@next=1

set@seed=len(@split)

set@location=charindex(@split,@str)

while@location0and@index>@next

begin

set@start=@location+@seed

set@location=charindex(@split,@str,@start)

set@next=@next+1

end

if@location=0select@location=len(@str)+1

--这儿存在两种状况：1、字符串不存在分隔符号2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字

符串后边有一个分隔符号。

returnsubstring(@str,@start,@location-@start)

END

selectdbo.Get_StrArrayStrOfIndex('8,9,4','',4)

--结合上边两个函数，象数组一样遍历字符串中的元素

createfunctionf_splitstr(@SourceSqlvarchar(8000),@StrSepratevarchar(100))

returns@temptable(F1varchar(100))

as

begin

declare@chasvarchar(100)

set@SourceSql=@SourceSql+@StrSeprate

while(@SourceSql0

Select@str=REPLACE(@str,

SUBSTRING(@str,@i,1),

NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))

,@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)

RETURN(@str)

END

GO

declare@s1varchar(8000)

select@s1='中２-３４５６a７８ＳＴＵＶabｎ中国ｏｐｗｘｙｚ'

selectdbo.f_convert(@s1,0),dbo.f_convert(@s1,1)

函数返回值是表

createtabletest(idintprimarykey,namechar(10))

insertintotestvalues(1,'test1')

insertintotestvalues(2,'test2')

insertintotestvalues(3,'test3')

insertintotestvalues(4,'test4')

1、标量函数

createfunctionreturn_count()

returnsint

as

begin

declare@countint

select@count=count(*)fromtest

return@count

end

--调用

selectdbo.return_count()cont--count为显示的列头

--运行结果

--count

--4

2、内嵌表值函数

createfunctionreturn_test()

returnstable

as

--begin内联表值函数不能用begin-end

returnselectnamefromtest

--end

--调用

select*fromreturn_test()

--运行结果

--name

--test1

--test2

--test3

--test4

3、多语句表值函数

createfunctionreturn_test_multi()

returns@temptable(idint,namechar(10))

as

begin

insertinto@tempselect*fromtestwhereidin(1,2)

return--记住，一定不要遗忘写return

end

--调用

select*fromdbo.return_test_multi()

--运行结果

--idname

--1test1

--2test2

在查询结果中增强一个自动增长的ID

selectid=identity(int,1,1),*into#TfromtestTable

select*from#T

droptable#T

sql删除重复的记录

打开测试数据库test，并以表w01为例，将下面的SQL语句放入sql2000查询分析器中，一段一段执行即可看

到效果

在sql2000下创建测试数据表

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[w01]')andOBJECTPROPERTY(id,N'IsUserTable')=1)

droptable[dbo].[w01]

在sql2022下创建测试数据表，假如是sql2022则用本段来推断数据表是否存在

ifexists(select1fromsys.tableswherename='w01')

droptablew01

开头创建测试数据库

GO

createtablew01(gs903varchar(32),gs1002varchar(32))

insertintow01

select'1','a'

unionallselect'1','a'

unionallselect'1','a'

unionallselect'2','a'

unionallselect'2','e'

unionallselect'3','b'

unionallselect'3','d'

go

select*fromw01

go

为表w01添加一个可以表示唯一标示的自增字段ID

altertablew01add[ID][int]IDENTITY(1,1)NOTFORREPLICATIONNOTNULL

查询删除前的数据和记录数：7

select*fromw01

selectcount(*)fromw01

查询具有重复记录的全部记录;3

selectgs903,gs1002,count(*)ascountfromw01groupbygs903,gs1002

havingcount(*)>1

orderbycountdesc

删除重复的数据：2行

deletefromw01whereidnotin(selectmax(id)fromw01groupbygs903,gs1002)

看看删除后还有没有重复记录：0

selectgs903,gs1002,count(*)ascountfromw01groupbygs903,gs1002

havingcount(*)>1

orderbycountdesc

删除后的数据和记录数：7-2=5

select*fromw01

selectcount(*)fromw01

用SQL语句添加删除修改字段

增强字段

altertabledocdspadddspcodechar(200)

删除字段

AlterTABLEtable_NAMEDropCOLUMNcolumn_NAME

修改字段类型

AlterTABLEtable_nameAlterCOLUMNcolumn_namenew_data_type

改名

sp_rename

更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。

语法

sp_rename[@objname=]'object_name',

[@newname=]'new_name'

[,[@objtype=]'object_type']

--假设要处理的表名为:tb

--推断要添加列的表中是否有主键

ifexists(selec