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 are the advantages of sql? Explain

1103

---

Explain commit, rollback and savepoint.

996

---

What is data modelling in sql?

1014

---

what is a primary key? : Sql dba

1033

---

What is java sql driver?

1114

---

Which is better trigger or stored procedure?

950

---

Is left join faster than inner join?

1123

---

What is execute immediate?

1082

---

Why do we use partitions in sql?

1046

---

Which is better join or inner query?

1046

---

what happens if null values are involved in expressions? : Sql dba

1039

---

what is user defined functions? : Sql dba

1031

---

How do you write an inner join query?

952

---

Which language is used in sql?

1055

---

what does the t-sql command ident_incr does? : Transact sql

1075

---