Stored Procedure

Question

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

Pravin More · Answer

hi vinit, 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 Saini · Answer

Thanks Pravin.....

Datta Kharad · Answer

Hi 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;

Stored Procedure

Answers (3)