How do you pass an output parameter to a stored procedure in SQL Server?
|
 Show
CodeProject In the past few days, we were discussing about different kinds of Templated Helpers in ASP.Net MVC. You can read that article here. For a change, I am switching over to SQL Server. Today, we will discuss about writing stored procedures with output parameters in SQL Server. Let’s understand this with an example. In the example, we will be using tblEmployee table. To create a stored procedure with output parameter, we use the keywords OUT or OUTPUT. @EmployeeCount in the below stored procedure is an OUTPUT parameter. Notice that it is specified with OUTPUT keyword. CREATE PROCEDURE spGetEmployeeCountByGender @Gender nvarchar(20), @EmployeeCount int Output AS BEGIN SELECT @EmployeeCount = COUNT(Id) FROM tblEmployee WHERE Gender = @Gender ENDTo execute this stored procedure with OUTPUT parameter, follow the below steps:
If you don’t specify the OUTPUT keyword, while executing the stored procedure, the @EmployeeTotal variable will be NULL. In the example below, we have not specified OUTPUT keyword. So while executing the stored procedure, a message of @EmployeeTotal is null is printed. DECLARE @EmployeeTotal int EXECUTE spGetEmployeeCountByGender ‘Female’, @EmployeeTotal IF(@EmployeeTotal is null) PRINT ‘@EmployeeTotal is null’ ELSE PRINT ‘@EmployeeTotal is not null’While using the parameter names, you can pass the parameters in any order. In the example below, we are first passing the OUTPUT parameter and then the input @Gender parameter. But we will get the total number of male employees without any errors. DECLARE @EmployeeTotal int EXECUTE spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = ‘Male’ PRINT @EmployeeTotalNow let’s have a quick look at some of the extremely useful system stored procedures.
Let’s see this in action. If we want to find out more information about the stored procedure we have just created, we can use sp_help spGetEmployeeCountByGender. While executing this, we could see the name of the stored procedure, type, created date, parameters, their data types, etc. You can use sp_help with any database objects like Tables, Views, Triggers, etc. For example, when we use sp_help with tblEmployee table, we will get all the information about the table like different columns present in the table, their data types, indexes associated with the table, constraints associated with the table, etc.
For example, when we use sp_helptext spGetEmployeeCountByGender, we will get the text of this stored procedure.
In the above example, sp_depends tblEmployee statement gives a result that there is a stored procedure which is dependent on tblEmployee table. So you have to be extremely careful while dropping this table. Reference
LicenseWritten By Software Developer
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT). Comments and Discussions
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages. How do you give a parameter to a stored procedure?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.
How do you pass input and output parameters in stored procedure?The value will be assigned in the stored procedure and returned to the calling statement. The following pass the OUTPUT parameter while executing the stored procedure. Above, the uspGetManagerID is called by passing INPUT parameter @employeeID = 2 and @managerID OUTPUT as the output parameter.
How use out parameter in stored procedure in SQL Server?Now create a stored procedure with an out parameter to insert data into the table. We create an error out parameter.. cmd. Parameters. Add("@ERROR", SqlDbType. ... . cmd. Parameters["@ERROR"]. ... . message = (string)cmd. Parameters["@ERROR"].. How do I declare an output parameter in SQL?Creating output parameters. parameter_name data_type OUTPUT. ... . CREATE PROCEDURE uspFindProductByModel ( @model_year SMALLINT, @product_count INT OUTPUT ) AS BEGIN SELECT product_name, list_price FROM production.products WHERE model_year = @model_year; SELECT @product_count = @@ROWCOUNT; END; ... . @product_count INT OUTPUT.. |
