Home > SQL SERVER > SqlServer # 2 – Return Values from Stored Procedures .

SqlServer # 2 – Return Values from Stored Procedures .

Hi Friends ,

We developers everyday come across many situations where we need to return the data  int the form of table or a single variable from the stored procedure.

In this article we will see three ways to return the data from the stored procedure

* Select Statements

* Output Variables

* Return Statement

 

** Select Statement

It is very easy to return the Result Sets [Note:We get Result Sets whenever we run simple SELECT statement in a stored procedure] from the stored procedure.

Let’s suppose you want a stored procedure to return a list of all the Students with a given last name.  The code for the stored procedure might look like this:
CREATE PROCEDURE dbo.GetStudentByLastName (@LastName NVARCHAR(50))
AS
SELECT Student_Id,FirstName,
      
LastName
FROM Student
WHERE LastName = @LastName
ORDER BY Student_Id

 

Suppose we pass parameter @LastName=’Desuza’ to above stored procedure we will get output as follows

 

exec GetStudentByLastName ’Desuza’ 

 

image

 

** Output Variable

Suppose in case we need to return a single value from the stored procedure in that case we can use it.

Example : – We need to return the FirstName of the Student table by passing the Student_Id as parameter

CREATE PROCEDURE dbo.GetLastNameByStudent_Id (
   
@Student_Id int,
   
@Student_First_Name varchar(max) OUTPUT )
AS
Select @Student_First_Name=FirstName from student
where Student_Id=@Student_Id

 

For instance we pass the @Student_Id parameter as 1.


DECLARE    @return_value int,
       
@Student_First_Name varchar(max)

EXEC    @return_value = [dbo].[GetLastNameByStudent_Id]
       
@Student_Id = 1,
       
@Student_First_Name = @Student_First_Name OUTPUT

SELECT    @Student_First_Name as N’@Student_First_Name’

SELECT    ‘Return Value’ = @return_value

GO

 
We will get OutPut as
 
image
 

** Return Statement

The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. Consider this procedure:

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue
GO

Consider we pass parameter as 1 to the procedure TestReturn

It will give output as

 

DECLARE    @return_value int

EXEC    @return_value = [dbo].[TestReturn]
       
@InValue = 1

SELECT    ‘Return Value’ = @return_value

GO

 
image

Enjoy the ways to return the values from a procedure !!!!!

Thanks.

For more interesting links and daily update please subscribe our link resource website
    Todays link are follows:
link-resource-14-august-8-august-10

Advertisements
Categories: SQL SERVER
  1. August 9, 2011 at 11:43 am

    nice pice Info man !!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: