.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Store Procedure Input and Output Parameters:

Posted By:Narayanan       Posted Date: August 13, 2012    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Here , We will Discuss about Store Procedure ,Input and Output Parameters in SQL.

Store Procedure:
Stored procedures are special objects available in sql server.
Its a precompiled statements where all the preliminary parsing operations are performed.
Its very fast when compared to ordinary sql statements.
Store Procedure Syntax:
Create Procedure <StoreProcedureName>
// your Query
Create Procedure sp_InsertemployeeDetails
@EmpId int,
@EmpName Varcha(20)
Insert into tbl_employee(empId,empName)values(@EmpId.@EmpName)
sp_InsertemployeeDetails -- Store Procedure Name
@EmpId int,@EmpName Varcha(20) -- Input Parameter.
as -- Keyword
Insert into tbl_employee(empId,empName)values(@EmpId.@EmpName) -- It is a Insert statement and Pass value to table Column using Input Parameter.
Input Parameter:
   placeholders for data that the user needs to send.
   input parameters are memory variables because they are stored in memory.  
How to Use Input Parameter in Store Procedure?
Create Procedure SP_ShowCustomer
@CityName varchar(50)
select firstname,lastname from tbl_Customer where location=@cityname
Just Run this Query using F5.
How to Execute this Query?
In Query window:
Exec  SP_ShowCustomer 'Chennai'
Select and the ablve line and Press F5 Key.
  Exec : To Execute the Query .
SP_ShowCustomer : Store Procedure name.
'Chennai' : passing value to Input Parameter.
    User can view the list of Customer first and last name of Chennai location .
Output Parameter:
  To return some value from it.they are defined with the word OUTPUT immediately afterward.
How to Use Output Parameter in Store Procedure?
Create Procedure SP_ShowCustomerName
@firstname varchar(50),
@lastname varchar(50),
@result varchar(150) OUTPUT
Set @result = @firstname + " " + @lastname
Just Run using F5.
How to Execute this Query?
In Query window:
DECLARE @res varchar(150)
Exec  SP_ShowCustomerName 'Lakshmii','Naraayanan',@res OUTPUT
PRINT @res
Select and the ablve line and Press F5 Key.
First , Declare a Variable for Output, Call that Object in the Exec and Print Output.
DECLARE @res varchar(150)
  Exec : To Execute the Query .
SP_ShowCustomerName: Store Procedure name.
'Lakshmii'  - First name
'Naraayanan', -- Last name
@res OUTPUT  -- Result using OUTPUT keyword
    User can view the Concatenate first and last name.
Output:Lakshmi Naraayanan.

 Thanks for Reading this Article and Any feedback Please send to this ID:naraayanan1983@gmail.com
 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend