SQl function Creation Instance Introduction _mssql

In SQL, the system has provided us with a very rich function: Example: Party function AVG, sum,count,max,min Date function: Day,month,year, etc. save a lot of time for our daily development but with some special requirements SQL also provides the function to create your own function. Here's a small example: requirements: An order form number, an order number, a product name to know the name of the product name of a certain order number separated by commas (,) as follows:

We create a title function: The StrName creation process is similar to creating a stored procedure as follows:

Right-click to create a table-valued function and a scalar function can be separated from the name a return table (set) title (single) in this small example because we only return the name, so create a scalar function code as follows:

Create FUNCTION [dbo]. [Orderdetailgetstrnamebyorderid] 
(
  @OrderId int
)
RETURNS varchar (+)
as
BEGIN
  --Declare
  The return variable DECLARE @StrPassengername varchar (+)
  set @StrPassengername = '
  Select @StrPassengername =@ strpassengername+ passengername + ', ' from OrderDetail where orderid= @OrderId
  select @StrPassengername =substring ( @StrPassengername, 0,len (@StrPassengername))--Return the result of the
  function return
  @StrPassengername End

Once created, you can use the same functions as avg,sum to reduce the amount of code below

Select DISTINCT A.orderid,
(SELECT [dbo].[ Orderdetailgetstrnamebyorderid] (A.orderid)) StrName from
OrderDetail A

Complete.