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 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 (a) 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 No 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 child nodes under the current node and write in the stored procedure

SELECT * from Testgetsubnodes (nodeId) can return the data in the table.

Write a scalar value function again

ALTER FUNCTION [dbo]. [Testgetsubnodes_]

(

@nodeId int

)

RETURNS int

As

BEGIN

DECLARE @nodeCount int

Select @nodeCount =5 from Menutree

Return @nodeCount

END

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 scalar-valued functions, it needs to be added to the owner, such as the owner is the dbo

Select Dbo.testgetsubnodes_, so that you can return 5, and if you do not add the dbo, then SQL will not recognize this function.

SQL Server's table-valued function returns a table type that is equivalent to a virtual table stored in memory.

Implementing a table-valued function is simple:
The following is a table-valued function with no input parameters

?

createfunction tvpoints()returns tableasreturn(select *fromtb_users);

The number of table-valued functions queries data for all user tables

For multi-statement table-valued functions, at BEGIN ... The function body defined in the END statement block contains a series of Transact-SQL statements,

These statements can generate a row and insert it into the table that will be returned.

The following example creates a table-valued function.

?

create  function  tvpoints () returns   @points table   (x float , y float as  begin insert   @points values insert   @points Values return end

Querying table-valued functions is the same as querying a normal table
SELECT * FROM Tvpoints ()
The return is a table

Table-valued functions with input parameters

?

createfunctiontvpoints2(@xASint,@yasint)returns@pointstable(xfloat, y float)asbegininsert@pointsvalues(@x,@y);return;end

SQL table-valued functions and scalar-valued functions