return string from stored procedure

Question

0.00/5 (No votes)

See more:

, +

Hi all,

I am new of the Ado.net.

I am writing sotred procedure and return string from sp. command successfully complete but when I execute error is came

my code is

SQL

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)

      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id

            SET @Message='record already exist'

            SELECT @Message
            return @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'

            SELECT @Message
            return @Message
        END
  END

when execute error came

SQL

(1 row(s) affected)
Msg 245, Level 16, State 1, Procedure GetSamples, Line 25
Conversion failed when converting the varchar value 'record does not exist' to data type int.

tel me where I went wrong.

Thanks in advance......

Posted 1-Apr-13 23:12pm

Bojjaiah

Add a Solution

Comments

vijay__p 2-Apr-13 5:21am

If you want to return only string then you need to remove "SELECT * FROM ..." query from SP otherwise it will retrurn two tables.
and you don't need to write return statement as you have used "SELECT" and just one thing are you using "ExecuteScaler" method?

Bojjaiah 2-Apr-13 5:27am

this is testing how can return string? like that

5 solutions

Solution 2

Play like this.......................

ALTER Procedure [dbo].[InsUpdGuest_Info]
@guest_id numeric(18, 0)=Null,
@membership_id numeric(18, 0)=null,
@guest_name nvarchar(100),
@contact1 nvarchar(20)=null,
@contact2 nvarchar(20)=null,
@landline nvarchar(20)=null,
@email nvarchar(50)=null,
@address nvarchar(500)=null,
@date_of_birth datetime=null,
@spouse_dob datetime=null,
@wed_aniversary datetime=null,
@reffered_by numeric(18, 0)=Null,
@IsActive bit = null,
@user_id numeric(18, 0)=null,
@RetVal As nvarchar(20)= Null output
As
Begin
If Not Exists(Select * From tblGuestInfo Where guest_id<>@guest_id And guest_name = @guest_name)
Begin
if Not Exists(Select * From tblGuestInfo Where guest_id=@guest_id)
Begin
Begin Try
begin tran;
DECLARE @InsertedRows TABLE (ID numeric(18,0))
Insert Into tblGuestInfo(membership_id
,guest_name
,contact1
,contact2
,landline
,email
,address
,date_of_birth
,spouse_dob
,wed_aniversary
,reffered_by
,IsActive
,created
,created_by)
/* To get output ID */
OUTPUT inserted.guest_id Into @InsertedRows
/*---------------*/
Values(@membership_id
,@guest_name
,@contact1
,@contact2
,@landline
,@email
,@address
,@date_of_birth
,@spouse_dob
,@wed_aniversary
,@reffered_by
,@IsActive
,getdate()
,@user_id);
Select @RetVal = Convert(nvarchar,ID) From @InsertedRows
commit;
return 1;
End Try
Begin Catch
rollback;
return 0;
End Catch
End
Else
Begin
Begin try
begin tran;
Update tblGuestInfo Set
membership_id = @membership_id,
guest_name = @guest_name,
contact1 = @contact1,
contact2 = @contact2,
landline = @landline,
email = @email,
address = @address,
date_of_birth = @date_of_birth,
spouse_dob = @spouse_dob,
wed_aniversary = @wed_aniversary,
reffered_by = @reffered_by,
IsActive = @IsActive,
updated = getdate(),
updated_by = @user_id
Where guest_id = @guest_id;

Delete From tblGuest_Family_Info Where guest_id = @guest_id;
Delete From tblGuest_Account Where guest_id = @reffered_by And trans_id=@guest_id And trans_type='REF';

Set @RetVal = Convert(nvarchar,@guest_id);
commit;
End try
Begin Catch
rollback;
Set @RetVal = 'False';
End Catch
End
End
Else
Begin
Set @RetVal = 'Already Existing';
End
End

Posted 1-Apr-13 23:30pm

Manish_Kumar_Nayak

Solution 3

If you want to just return the status message...alter your SP like this

SQL

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            
            SET @Message='record already exist'
 
            SELECT @Message
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
        END
  END

Posted 1-Apr-13 23:36pm

Naz_Firdouse

Solution 4

SP never return any varchar value.If you want to take @Message value in your application then try bellow code.

Alter PROC [GetSamples](@Id INT , @Message varchar(100) output)
AS
BEGIN
IF EXISTS(SELECT *
FROM Author
WHERE id = @Id)
BEGIN
SELECT *
FROM Author
WHERE id = @Id

SET @Message='record already exist'
SELECT @Message
END
ELSE
BEGIN
SET @Message='record does not exist'
SELECT @Message
END
END

Posted 1-Apr-13 23:39pm

Shobhana.n

Solution 5

define an output parameter as
@RetVal As nvarchar(20)= Null output

and assign whatever you want...

Posted 1-Apr-13 23:41pm

Manish_Kumar_Nayak

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-04-01T23:28:00

Solution 1

two ways...
1. you can use output parameter
2. use execute scalar.

your sp should be like below(for second option)

SQL

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[GetSamples]    Script Date: 04/02/2013 14:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[GetSamples](@Id INT)
AS
  BEGIN
      DECLARE @Message VARCHAR(250)
 
      IF EXISTS(SELECT *
                FROM   sample
                WHERE  id = @Id)
        BEGIN
            SELECT *
            FROM   sample
            WHERE  id = @Id
 
            SET @Message='record already exist'
 
            SELECT @Message
            return -- ***************change
        END
      ELSE
        BEGIN
            SET @Message='record does not exist'
 
            SELECT @Message
            return -- ***************change
        END
  END

Posted 1-Apr-13 23:28pm

Aarti Meswania

Comments

Bojjaiah 2-Apr-13 5:56am

hi aarti when I write c# it is not returning string my code is
try
{
string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;

using (SqlConnection SqlConn = new SqlConnection(strconn))
{
using (SqlCommand SqlComm = new SqlCommand("GetSamples", SqlConn))
{
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.Parameters.AddWithValue("@Id", 1);
var returnParameters = SqlComm.Parameters.Add("@Message", SqlDbType.VarChar);
returnParameters.Direction = ParameterDirection.ReturnValue;
SqlConn.Open();
SqlComm.ExecuteScalar();
var SPreturnValue = returnParameters.Value;
SqlConn.Close();
}
}
}
catch (SqlException ex)
{
}
catch (Exception ey)
{
}

Aarti Meswania 2-Apr-13 6:00am

try
{
string strconn = ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;

using (SqlConnection SqlConn = new SqlConnection(strconn))
{
using (SqlCommand SqlComm = new SqlCommand("GetSamples", SqlConn))
{
SqlComm.CommandType = CommandType.StoredProcedure;
SqlComm.Parameters.AddWithValue("@Id", 1);
SqlConn.Open();
string Message_ = SqlComm.ExecuteScalar(); // this will give you message
SqlConn.Close();
}
}
}
catch (SqlException ex)
{
}
catch (Exception ey)
{
}
Enter your reply belo

Bojjaiah 2-Apr-13 8:24am

Thank u Aarti
:)

5+

Aarti Meswania 2-Apr-13 8:49am

welcome!
Glad to help you! :)