Hii...
I know stored procedure return one or more than one value in sql server,But I want to know how to return more than one value ? Please explain with example ?
Thanks....
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Pravin MorePosted Dec 6, 2011, 11:44 PM
i didnt get how you want exactly but if want to more than one resultset then you can write that much queries.
like
create PROCEDURE [dbo].[Test_Proc]as
begin
select * from table1
select * from table2
end
or if you want to return values in variable the may be like below....
create PROCEDURE [dbo].[Test_Proc]
as
begin
declare @value1 varchar(max)
declare @value2 varchar(max)
select @value1= name from table1
select @value1= name from table2
select @value1
select @value2
end
thanks,
pravin.
Vineet Kumar SainiPosted Dec 7, 2011, 12:40 PM
Datta KharadPosted Dec 6, 2011, 11:56 PM
Creating dynamic stored proceudre to return values by taking input parameter in Oracle.
CREATE OR REPLACE PROCEDURE
UP_SELECTSP (
selColName IN VARCHAR, --selColName is used for selecting single column or multiple columns
TableName IN VARCHAR, --TableName is used for providing Table Name
whrCondition IN VARCHAR, --whrCondition is used for provide where condition
getResult_Cur OUT SYS_REFCURSOR --getResult_Cur is used for returning result set
)
AS
strSQL varchar(1000);
BEGIN
IF whrCondition is not null then
strSQL := 'SELECT ' || selColName || '
FROM ' || TableName || '
WHERE ' || whrCondition || '';
ELSE
strSQL := 'SELECT ' || selColName || '
FROM ' || TableName || '';
END IF;
OPEN getResult_Cur FOR strSQL;
END UP_SELECTSP;