SQLserver标量值函数使用例子

作者：袖梨 2022-06-29

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

 代码如下 复制代码

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)

)

AS

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

AS

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秒。

相关文章

精彩推荐