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.
