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
[catlist id=182].