{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Executing a stored procedure with OUTPUT parameters from Query window
Summary
Sometimes you need to execute a stored procedure from the query window to view the results in a quick and dirty way. This article shows how to execute a stored procedure that's already there in the db with output parameters.
 
Table of Contents

A Simple Query

A Query with OUTPUT parameters

 

A Simple Query

The SQL Server command that executes a stored procedure is EXEC (or the complete word EXECUTE). Actually, if all you are doing is executing a stored procedure as the first line, you don’t even need to use EXEC. Say, you have a stored procedure called test_GetList, you can just type that in the Query Window (of the SQL Server Management Studio), you will get the results back.

However, if you have other lines preceding the stored procedure, you need to use EXEC.

use mydb
go
exec test_GetList
go

In the above case, the stored procedure test_GetList is a simple SELECT command that returns a whole bunch of records. Something like the following.

ALTER PROCEDURE dbo.test_GetList
AS
      SELECT * FROM Customers;
      RETURN

A Query with OUTPUT parameters

Now let’s add an OUTPUT parameter to the above stored procedure. Here, we are getting the list of customers and separately a count of customers as well. You can use OUTPUT parameters to get back any number of other things.

In ASP.NET programming, you will try to get back the count separately as output variable sometimes because you might be getting only a partial list of customers but you want the entire count of the customer. In the example below, we are getting only the top 10 customers but we also want the count of entire list of customers.

ALTER PROCEDURE dbo.test_GetList
      @custCount int OUTPUT
AS
      SELECT @custCount=(SELECT COUNT(*) FROM Customers);
      SELECT TOP 10 * FROM Customers;
      RETURN

You can see where the above query would be useful. You would display only the top 10 customers, but tell the user that there are a total of 110 customers in the system, for example.

Now we want see if the above query works properly by testing it from the Query Window. You will use following lines to test the results from the above query.

 ECLARE @custCount int;
exec test_GetList @custCount OUTPUT;
PRINT @custCount;

The middle line above is executing the stored procedure. We are telling the db that @custCount is an OUTPUT variable. However, we need to declare that variable (as an integer, same as in the definition of the stored procedure). This declaration is done in the first line, by using DECLARE. And finally, the third line prints the value of the @custCount variable.

So, if you press F5 (for executing the query), you will see the results (list of top 10 customers in this case) in the Results tab. You will see the value of @custCount in the Messages tab. This is a quick way of using/testing if your stored procedure is working properly.

Bookmark and Share This

More Articles With Similar Tags
icon-fusion-sqlserver-on-start-menu.jpg
This article talks about using SQL Server in a virtual environment. Here the operating system installed is Windows Vista Ultimate on a Mac using VMware Fusion. The DBMS version used is SQL Server 2008. Adventure Works database is installed on top of this in the virtual environment and tested by issuing queries and creating database diagrams inside SQL Server Management Studio. Simple testing is also done with SQL Server Business Intelligence Development Studio on Windows virtual machine.
icon-connectionstrings.jpg
This article shows a quick and simple way to switch between local and production databases. Also discusses the connection strings.
icon-fusion-sqlserver-successful-installation.jpg
This article goes through installing SQL Server on Windows virtual machine. This Windows virtual machine has been created with VMware Fusion. The versions used here are SQL Server 2008 and Windows Vista Ultimate. Several screenshots of SQL Server installation on a virtual machine are also included.
You have defined a stored procedure that takes parameters. You have created an SqlDataSource that uses these parameters. Now how do you change these parameters at run-time from C# (or VB.Net) code?
About  Contact  Privacy Policy  Site Map