SQL Server stored procedure parameter both input and output
|
View Discussion Show Improve Article Save Article View Discussion Improve Article Save Article Stored Procedure: The stored procedure has the following key points as follows.
 Classification of Stored Procedure Create Stored Procedure without Parameter : You can use the following stored procedure given below to create the stored procedure without a parameter. create procedure sp_get_empno as begin select * from emp where ename='WARD' end exec sp_get_empnoCreate a Stored Procedure with Output Parameter : You can use the following stored procedure given below to create the stored procedure with an output parameter. Create a Stored Procedure with Input Parameter : You can use the following stored procedure given below to create the stored procedure with an Input parameter. USE Db1 GO CREATE PROCEDURE dbo.GetEmployeeID(@Email varchar(30)) AS SELECT * FROM employeeDetails WHERE email= @Email GOCreate a Stored Procedure with both input and Output Parameter : You can use the following stored procedure given below to create the stored procedure with both an input and output parameter. create procedure sp_get_empname(@name varchar(10) out, @id int) as begin select ename from emp where empno=@id end declare @en varchar(10) exec sp_get_empname @en,7521 print @en
This section contains information about general aspects of stored procedures usage. A stored procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only. Objects similar to stored procedures are stored functions. Almost everything that is true for procedures, holds for functions as well. The main difference between these objects is that function has a return value, and procedure has not. A stored procedures and functions may have input, output, and input/output parameters. Input parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module. For example, the following procedure inserts a row into the Dept table: CREATE PROCEDURE dept_insert (pDeptno INTEGER, pDname VARCHAR, pLoc VARCHAR) AS BEGIN INSERT INTO dept(deptno, dname, loc) VALUES (pDeptno, pDname, pLoc); END;It needs to receive the values to be inserted into the new record, and thus the procedure has three input parameters, corresponding to each field of the table. The procedure may be executed inside a PL/SQL block like follows: begin dept_insert (10, 'Accounting', 'New York'); end;Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable. In the following sample, the stored procedure returns the count of records in table Dept: CREATE PROCEDURE dept_count (cnt OUT INTEGER) AS BEGIN SELECT COUNT(*) INTO cnt FROM dept; END;An input/output parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. However, it can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable. Besides scalar variables, a stored procedure can return result sets, i.e. the results of a SELECT statement. In Oracle, the cursor variables are used for this case. A cursor may be interpreted as a reference to the result set. The following sample demonstrates how a simplest select statement can be wrapped in a stored procedure: CREATE PROCEDURE get_all_depts_proc (cur OUT SYS_REFCURSOR) AS BEGIN OPEN cur FOR SELECT * FROM dept; END;The same SELECT statement can be used via a stored function as follows: CREATE OR REPLACE FUNCTION get_all_depts_func RETURN SYS_REFCURSOR AS cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT * FROM dept; RETURN cur; END;Here the cursor is passed as the return value instead of being an output parameter. See AlsoWorking with Oracle Stored Procedures | Using Stored Procedures via the OracleCommand class | Using Package Procedures | Using Stored Procedures in DataSets Can a stored procedure use input and output parameters?A stored procedure can have zero or more INPUT and OUTPUT parameters. A stored procedure can have a maximum of 2100 parameters specified. Each parameter is assigned a name, a data type, and direction like Input, Output, or Return. If a direction is not specified, then by default, it is Input.
How can use input and output parameter in stored procedure in SQL Server?Input parameters allow the caller to pass a data value to the stored procedure or function. Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.
Can we pass two output parameter in stored procedure?A stored procedure can have many output parameters. In addition, the output parameters can be in any valid data type e.g., integer, date, and varying character. Second, after the SELECT statement, we assigned the number of rows returned by the query( @@ROWCOUNT ) to the @product_count parameter.
Can stored procedure have output parameter?The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.
|
