SQL Server 2000: Creating Stored Procedure with Input and Output Parameters

This tutorial explains how to create and use Stored Procedures with Input Parameters and output parameters along with the screen shots and sample code.

Creating Stored Procedure with Input Parameters

Input Parameters in Stored Procedures are placeholders for data that the user needs to send. Technically, input parameters are memory variables because they are stored in memory.


For creating Stored Procedure with Input Parameters, just change the above code to look like


CREATE PROCEDURE Show_Customer

@City varchar(50)

AS

SELECT FirstName, LastName FROM Customer

WHERE Location=@City

ORDER BY FirstName

Here the placeholder i.e. Input parameter is @City variable. It accepts the value, sends by the caller program and executes the procedure according to it. In this case, we can get all the Customer’s First and Last Names having location values resides in @City variable.

For executing, use the following code in Query Analyzer



USE TestDB

EXEC Show_Customer 'Oklahoma'


Here we have changed the city from “New York” to “Oklahoma” and now our stored procedure is now well generalized to return the Customers for variable cities, depends on the input value.

Creating Stored Procedure with Output Parameters

Now we are going to use Output parameters in our Stored Procedure to return some value from it. Output parameters are created in the same space as the input parameters, right between the procedure name and AS sections. The only difference is that they are defined with the word OUTPUT immediately afterward.


Open window for New Stored Procedure and add the following code in it,


CREATE PROCEDURE Show_Customer

@FirstNo varchar(50),

@SecondNo varchar(50),

@Result varchar(50) OUTPUT

AS

SET @Result = @FirstNo + @SecondNo



Don’t worry, I am explaining you another type of example due to the reason that it is much easier to understand how OUTPUT parameters work.

Executing the Stored Procedure having OUTPUT parameters is very simple. Just create a local variable to hold the returned value and display it.

Here is the code


USE TestDB

DECLARE @res int

EXEC Show_Customer 1,3 , @res OUTPUT

PRINT @res