Answer Posted / manikanta.srinu

Function can't return multiple value.
There are 3 types of functions are in sql server.
1.Scalar valued function:This function can return only one
value.
2.Inline table-valued functions:return a single table
variable that was created by a select statement.
3.Multitable valed function
:Returns a table variable whose structure was created by
hand, similar to a Create Table statement. It is useful
when complex data manipulation inside the function is
required.
1). Scalar Function
create function scalarvalue()
returns int
as
begin
return 1
end
Go
Scalar functions are usually deemed a performance hit,
because they are executed on a per-row basis.
Calling Function
select dbo.scalarvalue()

Note: We can create @temp variable in sclar function.
2). Inline table-valued functions
create function inlinetable()
returns table
as
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
ON a.SaleId = b.SaleId
INNER JOIN Production.Product c ON b.ProductID =
c.ProductID
WHERE a.ShipDate IS NULL
go

Calling Function
select * from dbo.fun1()

Note: We can’t create @temp variable in inline table
valued function.
2). Multi-statement table-valued functions
create function fun2()
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
DECLARE @MaxDate DATETIME

SELECT @MaxDate = MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID

INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate,
b.OrderQty
FROM Sales.SalesOrderHeader a INNER JOIN
Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c ON b.ProductID =
c.ProductID
WHERE a.OrderDate = @MaxDate
AND a.CustomerID = @CustomerID
RETURN
END
Calling Function
select * from dbo.fun2()
Note: We can create @temp variable in multi statement
table valued function.

What schema means?

888

---

How do you create an update query?

962

---

What is sql and its types?

1187

---

Why we use stored procedure instead of query?

998

---

Where can I learn sql for free?

987

---

What is record variable?

988

---

Why do we use subquery?

1014

---

What are its different types of dbms?

1005

---

What are synonyms in sql?

972

---

Explain foreign key in sql?

1022

---

how can we repair a mysql table? : Sql dba

979

---

What is sql prepared statement?

983

---

Does sap use sql?

993

---

What type of join is sql join?

982

---

What is graph sql?

991

---