SQL Server scalar function example

For example, when you delete a node, you need to delete all the subnodes under the current node. If the program only transmits one current node, then you need to write a function to get all the subnodes under the current node. The information of these subnodes can be put into a table and returned.

The code is as follows:

Copy code

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

The main function of this function is to return all the subnodes under the current node and write

The code is as follows:	Copy code
Select * from testGetSubNodes (nodeId) to return data in the table.

Write another scalar function

The code is as follows:	Copy code
Alter function [dbo]. [testGetSubNodes _] ( @ NodeId int ) RETURNS int AS BEGIN Declare @ nodeCount int Select @ nodeCount = 5 from MenuTree Return @ nodeCount END

This function returns an integer value and can be called in the stored procedure. However, the calling method is different, as the preceding table value function call does not require the owner, you only need to write the function name. For scalar functions, you need to add the owner. For example, the owner is dbo.

Select dbo. testGetSubNodes _, so that 5 is returned. If dbo is not added, the SQL statement does not recognize this function.

Call of scalar function

To call the ms SQL scalar function, you should add "dbo." before the function, otherwise the "not recognizable built-in function name" error will be reported. For example

 

The code is as follows:	Copy code
DECLARE @ WhichDB TINYINT; SELECT @ WhichDB = dbo. user_GetWhichDB (1); -- check which Database

 

In addition, the scalar value function is equivalent to a variable rather than a table. Therefore, writing this statement is incorrect:

   

The code is as follows:	Copy code
SELECT * FROM dbo. user_GetWhichDB (1 );

It should be written as follows:

The code is as follows:	Copy code
SELECT dbo. user_GetWhichDB (1 );

Add alias:

The code is as follows:	Copy code
SELECT dbo. user_GetWhichDB (1) AS FieldName;

 

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

-- Scalar value function

The code is as follows:	Copy code
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

Example

Scalar function:

The code is as follows:	Copy code
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 On J. ApplicationID = A. ApplicationID WHERE A. TeamID = 19 AND T. PriorityID = @ Priority) END

Call the function:

The code is as follows:

Copy code

Declare @ result varchar (200) Declare @ message VARCHAR (200) Declare @ priorityID int -- Define a cursor Declare T_cursor cursor for select PriorityName from tbPriority -- Open the cursor Open T_cursor SET @ message ='' Fetch next from T_cursor INTO @ RESULT IF @ FETCH_STATUS <> 0 PRINT 'xzc' WHILE @ FETCH_STATUS = 0 -- Traverse the content in the cursor and assign a value to the variable BEGIN PRINT @ RESULT -- Get @ priorityID Set @ priorityID = (select PriorityID from tbPriority where PriorityName = @ RESULT) -- Call a scalar function to obtain the returned value and convert the data type Set @ RESULT = CONVERT (varchar (20), dbo. MyFunction (@ priorityID )) Select @ message = @ message + @ RESULT + ',' Fetch next from T_cursor INTO @ RESULT END PRINT @ message -- Close the cursor CLOSE T_cursor DEALLOCATE T_cursor

Convert datetime to tick

Program code

The code is as follows:

Copy code

Create function getTicks (@ time datetime) returns bigint As Begin Declare @ mintime datetime, @ today datetime Declare @ days bigint, @ milliseconds bigint Declare @ ticks bigint Select @ mintime = '2014-01-01 ', @ today = dateadd (day, datediff (day, 0, @ time), 0) -- 639905-01-01 and 1753-01 differ by days 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

FAQs

Q: Why not use datediff (millisecond, '2017-01-01 ', @ time) * 0001 to get the tick value?
A: There are two reasons. One is that the date range of the dateime type is January 1, January 1-9, 1753, December 31, 999, and the other is the datediff function. When datepart is millisecond, the maximum difference between the two parameters is 24 days, 20 hours, 31 minutes, 23.647 seconds.