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 is normalisation and its types?

954

---

What is an invalid partition table?

958

---

How do you remove duplicate records from a table?

884

---

what are date and time intervals? : Sql dba

998

---

Is like operator in sql case sensitive?

960

---

What is difference between sql and oracle?

1046

---

What can sql server reporting services do?

1053

---

Explain how can you save or place your msg in a table?

979

---

What has stored procedures in sql?

991

---

What is bulk collect in pl sql?

1016

---

What are the different types of triggers?

1051

---

What jobs use sql?

924

---

What is time based sql injection?

900

---

What is a ddl command?

1004

---

What are the dml statements?

1094

---