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

how to return multiple values from stored procedure in sql server

Jul 31, 2012
Introduction:

In this article I will explain how to create a stored procedure to return value or multiple values in SQL server.

Description:

In previous post I explained how to write stored procedure to return output parameter values in sql server and many articles relating to SQL Server. Now I will explain how to create a stored procedure to return values in SQL Server. To implement this concept first design table in database and give name as UserDetails as shown below

Column Name
Data Type
Allow Nulls
UserId
int(set identity property=true)
No
UserName
varchar(50)
Yes
FirstName
varchar(50)
Yes
LastName
Varchar(50)
Yes
After completion table design enter some data like as shown below

Once table creation done write the stored procedure like as shown below


CREATE PROCEDURE GetUserDetails
@UserName VARCHAR(50),
@Result INT OUTPUT
AS
BEGIN
SELECT UserId FROM UserDetails WHERE UserName=@UserName
END
Now we want to get the value return by procedure GetUserDetails for that write the query like as shown below


DECLARE @UserId INT
EXEC GetUserDetails 'SureshDasari',@Result=@UserId OUTPUT
Once we run above query we will get data like as shown below

OutPut

In this way we can return value in stored procedure in SQL server. Here I will explain one interview question What is difference between Stored procedure and fuction in SQL Server?

If anyone asks this question first we will say that stored procedure will return multiple values but function will return only one value. 

How stored procedure will return multiple values? Now I will explain how to write stored procedure to return multiple values in SQL Server for that first write stored procedure as shown below


CREATE PROCEDURE GetMultipleUserDetails
@UserName VARCHAR(50),
@Id INT OUTPUT,
@lName VARCHAR(50) OUTPUT
AS
BEGIN
SELECT UserId,LastName FROM UserDetails WHERE UserName=@UserName
END
Now we want to get the multiple values return by procedure GetMultipleUserDetails for that write the query like as shown below


DECLARE @UserId INT,@LastName VARCHAR(50)
EXEC GetMultipleUserDetails 'SureshDasari',@Id=@UserId OUTPUT, @lName= @LastName OUTPUT
Once we run above query we will get output like as shown below

OutPut

This way we can return multiple values with stored procedure but in function we don’t have a chance to return not more than one value.

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

13 comments :

Anonymous said...

It was a very good post by you

Anonymous said...

really ur blos is very good .it is very usefull for us.

Anonymous said...

Excellent!!!!!!!!!!!!!!!!!!!!!

Anonymous said...

thanks a lot

Anonymous said...

DECLARE @UserId INT
EXEC GetUserDetails 'SureshDasari',@Result=@UserId OUTPUT

sir, i am new to this so plz tell me where to write this above query in visual studio for getting the result

Unknown said...

THANKS BRO

rohit said...


CREATE PROCEDURE GetMultipleUserDetails
@UserName VARCHAR(50)

AS
BEGIN
SELECT UserId,LastName FROM UserDetails WHERE UserName=@UserName
END


EXEC GetMultipleUserDetails 'SureshDasari'

also gives same result... then what is need to declare OUTPUT keyword


Anonymous said...

how can i get more than one value from from different tables

Anonymous said...

hi sir
is it possible use this procedure for update data?

Unknown said...

Nice post.

QAQW said...
This comment has been removed by the author.
QAQW said...

Nice Post...Could you please clarify below my doubt?

EXEC GetMultipleUserDetails 'SureshDasari'

also gives same result... then what is need to declare OUTPUT keyword.

Anonymous said...

rattuuu popat hai..yeh suresh..sab scenarios check kar

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 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.