Differences Between SQL table-valued functions and scalar-valued functions

Differences Between SQL table-valued functions and scalar-valued functions

Write SQL stored procedures often need to call some functions to make the process more reasonable, but also to make the function more reusable, but when writing SQL functions may find that some functions are written under the table-valued function, some are written under a scalar value, the difference is that the table-valued function can only return a table, scalar-valued functions may return the base type. For example, when a user deletes a node, it is necessary to delete all the child nodes under the current node, if the program only passes a current node, it is necessary to write a function to get all the child nodes under the current node, the information of these child nodes can be placed in a table to return.

 ALTER FUNCTIONTestgetsubnodes (@nodeId int)RETURNS @t TABLE(IDbigint Identity(1,1) not NULL, Nodeidsint, NodeNamevarchar( -)) asBEGIN    Insert  into @t Values(@nodeId,'Header');  while exists(SelectNodeid fromDbo. TreewhereParentIDinch(SelectNodeids from @t) andNodeid not inch(SelectNodeids from @t))    begin        Insert  into @t SelectNodeid, NodeName fromDbo. TreewhereParentIDinch(SelectNodeids from @t)    End     RETURN END

The main function of this function is to return all the child nodes under the current node, and write select * from Testgetsubnodes (nodeId) in the stored procedure to return the data in the table. Write a scalar value function again

 ALTER FUNCTION [dbo].[Testgetsubnodes_](    @nodeId int)RETURNS int asBEGIN    Declare @nodeCount int    Select @nodeCount=5  fromMenutreereturn @nodeCountEND

This function simply returns an integer value, which can then be called in a stored procedure, but is called differently, as the table-valued function call above does not require an owner, as long as the function name is written, and for a scalar-valued function, it needs to be added to the owner, such as the owner is Dboselect Dbo.testgetsubnodes_, so you can return 5, and if you don't add the dbo, then SQL will not recognize this function.

Differences Between SQL table-valued functions and scalar-valued functions