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
?
create function tvpoints() returns table as return ( select * from tb_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
?
create
function
tvpoints2(@x
AS
int
,@y
as
int
)
returns
@points
table
(x
float
, y
float
)
as
begin
insert
@points
values
(@x,@y);
return
;
end
|
SQL table-valued functions and scalar-valued functions