How to create a stored procedure in sqlserver to return value by taking input parameter

Question

Hi How to create a stored procedure in sqlserver to return value by taking input parameter..

Datta Kharad · Answer

Hi Creating dynamic stored proceudre to return value by taking input parameter. 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;

Pravin More · Answer

Hi Akila here is sample for it.... create PROCEDURE [dbo].[GetValueONID] @ID INT AS BEGIN SET NOCOUNT ON select p.Amount from Product p where p.productID=@ID END Thanks Pravin.

Krishna Garad · Answer

Create Procedure Sp_Name ( @para1 datatype @para2 Datatype out ) As Begin Set @para2=(value) End