Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Pass Output Parameter to Stored Procedure in SQL Server

Jul 17, 2016
Introduction:

Here I will explain how to send / pass output parameter to stored procedure in sql server or how to use stored procedure with output parameters in sql server with example or return data from stored procedure using output parameter in sql server. By using OUT datatype we can send output parameter to stored procedure in sql server.

Description:


We need to write stored procedure like as shown below to pass / return output parameter in sql server.

Stored Procedure with Output Parameters

Following is the stored procedure to return output parameters in sql server.


CREATE PROCEDURE sp_userinformation
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(50),
@PhoneNo varchar(50),
@Location varchar(50),
@Created_By varchar(50),
@ERROR VARCHAR(100) OUT

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
               
SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

IF NOT EXISTS(SELECT * FROM User_Information WHERE UserName=@UserName)
BEGIN
INSERT INTO User_Information
(
UserName,
[Password],
FirstName,
LastName,
Email,
PhoneNo,
Location,
Created_By
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName,
@Email,
@PhoneNo,
@Location,
@Created_By
)
--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'
END
ELSE
BEGIN
--If User already Exists i am returning this Message as Output Parameter
SET @ERROR=@UserName + ' Already Exists'
END
END

If you observe above sql server stored procedure, we are sending "@ERROR" as output parameter. At the end of stored procedure result will be sent back to application "@ERROR" parameter. This is how we need to write queries to return output parameters.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

3 comments :

Alexander Grinvald said...

Thanks for clear explaining

Sachin Godase said...

best and simple explanation for output parameter

Anonymous said...

bhj,

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.