In the past few days, we were discussing about different kinds of Templated Helpers in ASP.

Global Variables and Stored Procedure Parameters

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. 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. In the example below, we have not specified OUTPUT keyword. So while executing the stored procedure, a message of EmployeeTotal is null is printed.

While using the parameter names, y ou 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. While executing this, we could see the name of the stored procedure, type, created date, parameters, their data types, etc.

Returning stored procedure parameter values to a calling stored procedure

So you have to be extremely careful while dropping this table. This article, along with any associated source code and files, is licensed under The Code Project Open License CPOL. Articles Quick Answers Messages.

SQL Server — How to Write Stored Procedures With Output Parameters? Arun Ramachandran India , 9 Jul Please Sign up or sign in to vote. How to write stored procedures with output parameters in SQL Server. CodeProject In the past few days, we were discussing about different kinds of Templated Helpers in ASP. First, initialise a variable of same datatype as that of the output parameter. Here, we have declared EmployeeTotal integer variable.

Then pass the EmployeeTotal variable to the stored procedure. You have to specify the OUTPUT keyword. Then execute the stored procedure. Used to view the information about the stored procedure like parameter names, their datatypes, etc. Used to view the Text of the stored procedure.

Used to view the dependencies of the stored procedure. This system stored procedure is very useful, especially if you want to check whether there are any stored procedures that are referencing a table which you are about to drop. Reference Arun Ramachandran http: Arun Ramachandran India Software Developer. Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India.

sql - Stored Procedure with both input and output parameters - Stack Overflow

He has written over 95 articles on the subject on his blog at http: Overview of SQL Server Stored Procedure. Generate and add keyword variations using AdWords API. Wrapper Generator for SQL Server Stored Procedures. Window Tabs WndTabs Add-In for DevStudio. SAPrefs - Netscape-like Preferences Dialog. WTL for MFC Programmers, Part IX - GDI Classes, Common Dialogs, and Utility Classes. You must Sign In to use this message board. Carlo Alfio Flores Jul 3: Permalink Advertise Privacy Terms of Use Mobile Web02 2.

Thanks and it's working fine. My vote of 5 CatchExAs 9-Jul 3: Nice, I learnt something new.