Answer / 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.

Answer / rahul poptani

Function returns only 1 value
BUT you can return multiple value Indireclty..
1) By returning an result set -

CREATE OR REPLACE FUNCTION GET_DEPT_INFO (P_DEPTNO IN NUMBER) RETURN SYS_REFCURSOR
AS
V_RC SYS_REFCURSOR;
BEGIN
OPEN V_RC FOR SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO = P_DEPTNO;
RETURN V_RC;
END;

SQL> VAR RESULTSET REFCURSOR;
SQL> EXEC :RESULTSET := GET_DEPT_INFO(10)

PL/SQL procedure successfully completed.
SQL> PRINT :RESULTSET

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

2)PROVIDING OUT MODE PARAMETERS IN FUNCTION (RARELY USED)

SQL> CREATE OR REPLACE FUNCTION OUTMODE_EXAMPLE (P_DEPTNO NUMBER, P_DNAME OUT VARCHAR2)
2 RETURN NUMBER
3 AS
4 BEGIN
5 SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO = P_DEPTNO;
6 RETURN NULL;
7 END;
8 /

Function created.

SQL> DECLARE
2 V_DNAME DEPT.DNAME%TYPE;
3 V NUMBER;
4 BEGIN
5 V := OUTMODE_EXAMPLE(10,V_DNAME);
6 DBMS_OUTPUT.PUT_LINE(V_DNAME);
7 END;
8 /
ACCOUNTING

PL/SQL procedure successfully completed.

Is it possible for a table to have more than one foreign key?

0 Answers  

---

how will u find statistics of a database objects?

2 Answers   iFlex,

---

What does the file extension accdb stand for?

0 Answers  

---

how can you see all indexes defined for a table? : Sql dba

0 Answers  

---

what the meaning of sql

8 Answers  

---

---

What is the main difference between a UNION statement and a UNION ALL statement? 1. A UNION statement eliminates duplicate rows; a UNION ALL statement includes duplicate rows. 2. A UNION statement can be used to combine any number of queries; a UNION ALL statement can be used to combine a maximum of two queries. 3. A UNION statement can only combine queries that have parallel fields in the SELECT list; a UNION ALL statement can combine queries with differing SELECT list structures. 4. A UNION statement cannot be used with aggregate functions; a UNION ALL statement can be used with aggregate functions. 5. There is no difference between the two statements; they are interchangeable.

2 Answers   Saman Bank, Sonata,

---

Where not exists in sql?

0 Answers  

---

How many database objects (trigger, packages, sequence etc) uses a particular field in a given table. For ex: I want to know how many database object uses the ATTRIBUTE1 in the PO_VENDORS table. What query will give me the result showing the database object name(package, trigger etc), field_name used (in this case ATTRIBUTE1) and table_name (in this case PO_VENDORS).

2 Answers   IBM,

---

What is the difference between a subquery and a join?

0 Answers  

---

What are the different types of dbms?

0 Answers  

---

table - new_no old_no 2345 1234 3456 2345 5678 4567 output sud be -new_no 1234 2345 3456 4567 5678

2 Answers  

---

Name the operator which is used in the query for pattern matching?

0 Answers  

---