SQLserver标量值函数使用例子

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

    DECLARE @WhichDB TINYINT;
    SELECT @WhichDB = 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

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