SQLserver标量值函数使用例子

时间：2022-06-29 07:57:54 编辑：袖梨来源：一聚教程网

举个例子，当用户删除一个节点的时候，是需要将当前节点下的所有子节点都删掉，如果程序只传一个当前节点，那就需要写一个函数来得到当前节点下的所有子节点，这些子节点的信息就可以放到一个表中返回。

代码如下

复制代码

ALTER FUNCTION testGetSubNodes

(

-- Add the parameters for the function here

@nodeId int

)

RETURNS

@t TABLE

(

-- Add the column definitions for the TABLE variable here

id bigint identity(1,1) not null,

nodeIds int ,

nodeName varchar(500)

)

BEGIN

-- Fill the table variable with the rows for your result set

insert into @t values(@nodeId,'header');

while exists(

select nodeid from dbo.Tree where parentid

in (select nodeIds from @t) and nodeid not in(select nodeIds from @t))

begin

insert into @t select nodeid, nodename from dbo.Tree where parentid

in (select nodeIds from @t)

end

RETURN

END

这个函数的主要功能就是返回当前节点下的所有子节点，在存储过程中写

代码如下	复制代码
select * from testGetSubNodes(nodeId)就可以返回表中的数据了。

再写一个标量值函数

代码如下

复制代码

ALTER FUNCTION [dbo].[testGetSubNodes_]

(

@nodeId int

)

RETURNS int

BEGIN

declare @nodeCount int

select @nodeCount=5 from MenuTree

return @nodeCount

END

这个函数很简单返回一个整型值，然后就可以在存储过程中调用了，不过调用的方式有所不同，象上面的表值函数调用是不需要所有者的，只要写函数名称就可以，对于标量值函数来说，是需要加上所有者的，比如所有者是dbo

select dbo.testGetSubNodes_,这样就可以返回5，如果不加dbo，那sql会不认识这个函数。

标量值函数的调用

调用 MS SQL 标量值函数，应该在函数前面加上 "dbo."，否则会报 “不是可以识别的内置函数名称”错误。例如

代码如下	复制代码
DECLARE @WhichDB TINYINT; SELECT @WhichDB = dbo. user_GetWhichDB(1);--看看是哪个数据库的

另外，标量值函数就相当于一个变量，而不是一个表，所以这样写是错误的：

代码如下	复制代码
SELECT * FROM dbo. user_GetWhichDB(1);

应该这样写：

代码如下	复制代码
SELECT dbo. user_GetWhichDB(1);

加上别名：

代码如下	复制代码
SELECT dbo. user_GetWhichDB(1) AS FieldName;

=================================================

--标量值函数

代码如下	复制代码
ALTER FUNCTION [dbo].[user_GetWhichDB] ( @UserId INT = 0 ) RETURNS TINYINT WITH EXECUTE AS CALLER AS BEGIN DECLARE @WhichDB TINYINT; SET @WhichDB = 1; IF @UserId >= 115098 SET @WhichDB = 2; RETURN (@WhichDB); END

例子

标量值函数:

代码如下

复制代码

ALTER FUNCTION MyFunction
(
@Priority int
)
RETURNS int
AS
BEGIN
RETURN(select count(T.TicketID) 'Total Number of Tickets'
from tbTicket T inner join tbJob J
on T.JobID = J.JobId inner join tbApplication A
on J.ApplicationID = A.ApplicationID
WHERE
A.TeamID =19
AND
T.PriorityID = @Priority)

END

调用函数:

代码如下

复制代码

Declare @RESULT VARCHAR(200)
Declare @message VARCHAR(200)
declare @priorityID int
--定义游标
declare T_cursor cursor for select PriorityName from tbPriority
--打开游标
open T_cursor

SET @message=''

FETCH NEXT FROM T_cursor INTO @RESULT
IF @@FETCH_STATUS <> 0
PRINT 'xzc'

WHILE @@FETCH_STATUS = 0
--遍历游标中内容,赋值给变量
BEGIN
PRINT @RESULT
--得到@priorityID
set @priorityID=( select PriorityID from tbPriority where PriorityName = @RESULT)
--调用标量函数,获取返回值,转换数据类型
set @RESULT=CONVERT(varchar(20),dbo.MyFunction(@priorityID))
select @message =@message + @RESULT + ','
FETCH NEXT FROM T_cursor INTO @RESULT
END
PRINT @message
--关闭游标
CLOSE T_cursor
DEALLOCATE T_cursor

datetime转为tick

程序代码

代码如下

复制代码

create function getTicks(@time datetime) returns bigint
as
begin
    declare @mintime datetime, @today datetime
    declare @days bigint, @milliseconds bigint
    declare @ticks bigint

    select @mintime='1753-01-01', @today=dateadd(day,datediff(day,0,@time),0)
    --0001-01-01和1753-01-01相差639905天
    select @days=datediff(day,@mintime,@today)+639905, @milliseconds=datediff(millisecond,@today,@time)
    select @ticks=(@days*24*60*60*1000 + @milliseconds)*10000

return @ticks
end
go

常见问题

问：为什么不直接用datediff(millisecond,'0001-01-01',@time)*10000得到tick值？
答：原因有两点，一是dateime类型日期范围为1753年1月1日到9999年12月31日，二是datediff函数，datepart为millisecond时，两个参数的最大差值为24天20小时31分钟23.647秒。

推荐专题

最新下载

热门教程

SQLserver标量值函数使用例子

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程