how to return multiple values from stored procedure in sql server

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 Email