Exforsys.com
 

Sponsored Links

 

SQL Server Tutorials

 
Home Tutorials SQL Server
 

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






Read Next: SQL Server 2000 Training Details



 

 

Comments


G.N.S. said:

  Although a seasoned pro in sql server and just stumbled on it i found i to be very informative and right to the point, good job m8!
January 30, 2007, 4:27 am

p.anandaraj said:

  i like your article.i want more advance concept in procedure
November 26, 2008, 4:47 am

Rajesh Subramanian said:

  it was a good article..thanks so much
January 31, 2009, 4:25 pm

harsha said:

  Hi,
When we have a procedure and when we are taking the result in a varaible, I do not want to return that value by another procedure.

create proc proc1 (@a int)
as
begin

if (@a = 1)
select 1
else 0

end

create proc2
as
begin

declare @var tinyint
exec @var = proc1 2

if (@var > 0)
begin
select 'greater than 0'
end
end

Now when i execute proc2 there would be 2 values return .. I do not want 2 values to be return..
Anybody have any idea how to make the proc not return 2 values..
July 14, 2009, 6:27 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape