Differences between SQL table valued functions and scalar functions

Differences between SQL table valued functions and scalar functions

 

When writing SQL stored procedures, you often need to call some functions to make the processing process more reasonable and make the function more reusable. However, you may find that when writing SQL functions, some functions are written under the table value function and some are written under the scalar value. The difference is that the table value function can only return one table, and the scalar value function can return the base type. 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. 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 Select * From testgetsubnodes (nodeid) to return data in the table. Write another scalar function 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.   The following is an example written by myself: Set ansi_nulls on Go Set quoted_identifier on Go -- ===================================================== ====== -- Author: <yuzt> -- Create Date: <2010-01-20 :27> -- Type: Multi-statement table Value Function -- Description: <determines whether the time of multiple retries is within the settlement period. You can return 0 if the record is returned within the period. Otherwise, 1 is returned.> -- Call method: Select * From DBO. readytestfunc ('2017-2-28 ', 2010) -- Select * From DBO. f_isoverfeedbacktime ('2017-2-28 ', 2009) -- ===================================================== ====== Alter function DBO. f_isoverfeedbacktime ( @ Applyordertime datetime, @ Regionid int, @ Moduleid int ) Returns @ Table (bsettle datetime, esettle datetime, returnval INT) As Begin -- Fill the table variable with the rows for your result set Declare @ ayear int -- the order submission time is one year. Declare @ amonth int -- order submission time month Declare @ aday int -- order submission date   Declare @ beginday int -- settlement start day Declare @ endday int -- settlement deadline   Declare @ bsettlementtime datetime; ---- settlement cycle Start Time Declare @ esettlementtime datetime; ---- settlement cycle end time   Declare @ nextyear int ---- next year Declare @ preyear int ---- previous year Declare @ nextmonth int ---- next month Declare @ premonth int ---- last month   Declare @ returnval int -- Return Value   Set @ ayear = year (@ applyordertime) Set @ amonth = month (@ applyordertime) Set @ aday = Day (@ applyordertime)   Select @ beginday = begindate, @ endday = enddate from config_billingcycle where moduleid = @ moduleid and regionid = @ regionid and isvalid = 1 If (@ aday <= @ beginday and @ aday <= @ beginday) -- last month and this month (both smaller) Begin Set @ premonth = @ Amonth-1 If (@ premonth <= 0) --- situation of February Begin Set @ preyear = @ ayear-1 Set @ bsettlementtime = convert (varchar (4), @ preyear) + '-12' +'-'+ convert (varchar (4), @ beginday) Set @ esettlementtime = convert (varchar (4), @ ayear) + '-01' +'-'+ convert (varchar (4), @ endday) End Else Begin Set @ bsettlementtime = convert (varchar (4), @ ayear) + '-' + convert (varchar (4), @ premonth) + '-' + convert (varchar (4), @ beginday) Set @ esettlementtime = convert (varchar (4), @ ayear) + '-' + convert (varchar (4), @ amonth) + '-' + convert (varchar (4), @ endday) End End If (@ aday >=@ beginday and @ aday >=@ beginday) -- this month and next month (greater) Begin Set @ nextmonth = @ amonth + 1 If (@ nextmonth> = 13) ---- situation in January Begin Set @ nextyear = @ ayear + 1 Set @ bsettlementtime = convert (varchar (4), @ ayear) + '-12' +'-'+ convert (varchar (4), @ beginday) Set @ esettlementtime = convert (varchar (4), @ nextyear) + '-01' +'-'+ convert (varchar (4), @ endday) End Else Begin Set @ bsettlementtime = convert (varchar (4), @ ayear) + '-' + convert (varchar (4), @ amonth) + '-' + convert (varchar (4), @ beginday) Set @ esettlementtime = convert (varchar (4), @ ayear) + '-' + convert (varchar (4), @ nextmonth) + '-' + convert (varchar (4), @ endday) End End If (@ bsettlementtime <= getdate () and @ esettlementtime> = getdate ()) Begin Set @ returnval = 0 End Else Begin Set @ returnval = 1 End Insert @ table select @ bsettlementtime, @ esettlementtime, @ returnval Return End Go