Return a string Value from SQL Server stored procedure

Question

1.00/5 (2 votes)

See more:

C# code is

public void GetUserLoginName(string Username)
{

SqlConnection sqlcon;
SqlCommand sqlcmd;
try
{
sqlconnstring = ConfigurationManager.ConnectionStrings["CONERP"].ConnectionString;
sqlcon = new SqlConnection(sqlconnstring);
sqlcon.Open();
sqlcmd = new SqlCommand("GetUserLoginName", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = Username;
SqlParameter rolename = sqlcmd.Parameters.Add("@role", SqlDbType.VarChar);
rolename.Direction = ParameterDirection.Output;
Session["Loginusername"] = Convert.ToString(rolename.Value) ;
sqlcmd.Dispose();
sqlcon.Close();

}
catch (SqlException sqlerr)
{

}
}
and sql stored procedure is

USE [COERP]
GO
/****** Object: StoredProcedure [dbo].[GetUserLoginName] Script Date: 03/07/2014 13:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetUserLoginName](
@role varchar(max) out,
@Username varchar(max)
)

as
begin
set @role =(select Name from Newuser where Username=@Username)
end

the output of this code is '@role' not the value inside @role !!

Posted 6-Mar-14 19:26pm

Sidharth R

Updated 6-Mar-14 21:17pm

v3

Add a Solution

Comments

Raajkumar.b 7-Mar-14 1:54am

check this link http://www.sqlteam.com/article/stored-procedures-returning-data
Replace these lines with below code

sqlcmd.Parameters.Add("@role", SqlDbType.VarChar).Direction = ParameterDirection.Output;
string name = Convert.ToString(sqlcmd.ExecuteReader().ToString());

SqlParameter rolename= cmd.Parameters.Add("@role", SqlDbType.varchar);
rolename.Direction = ParameterDirection.Output;
you can get your result in rolename.

Sidharth R 7-Mar-14 2:11am

my store procedure is

USE [COERP]
GO
/****** Object: StoredProcedure [dbo].[GetUserLoginName] Script Date: 03/07/2014 12:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetUserLoginName](
@role varchar(max) output ,
@Username varchar(max)
)

as
begin
select @role = ( Select Name from Newuser where Username=@Username)

end

is there any error in this ??

Raajkumar.b 7-Mar-14 2:22am

select @role = ( Name from Newuser where Username=@Username)

Sidharth R 7-Mar-14 3:14am

but im getting the out put as '@role' ?? not the value inside that ..!!

Raajkumar.b 7-Mar-14 3:28am

what are fields of Newuser Table

Sidharth R 7-Mar-14 3:35am

Sno as bigint
Username as nvarchar(max)
pass
name as nvarchar(max)
emailid
roleid

Raajkumar.b 7-Mar-14 4:11am

stored procedure
alter procedure GetUserLoginName(

@Username varchar(max),@role varchar(max) output)
as
begin

select @role = (select name from Tb_newuser where Username=@Username)
return
end
query works properly by testing it from the Query Window
DECLARE @role varchar(max);
exec GetUserLoginName 'rajesh',@role OUTPUT;
PRINT @role;

Sidharth R 7-Mar-14 4:25am

anyways thanks for the help i got the solution ...
i just changes the code as

set @role = CONVERT(VARCHAR(250),(select Name from Newuser where Username=@Username))

and the program worked well :D

Thank you for your valuable time..
-Sid-

3 solutions

Solution 1

Two things you need to change:

1. "Out" should be added in your Stored Procedure to @role declaration which is missing.
2. Instead of returning just Select or SET the value of @role (if you are assigning a value to it)

Posted 6-Mar-14 19:35pm

Sanket Saxena

Solution 3

Have a look here: CREATE PROCEDURE[^] and go to C. Using OUTPUT parameters section ;)
And here[^] you'll find information about how to execture SP ;)

Posted 6-Mar-14 19:57pm

Maciej Los

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)

JoCodes · Accepted Answer · 2014-03-06T19:52:00

Solution 2

Refer
How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet[^]

get-out-parameter-from-a-stored-procedure-in-Asp-Net/[^]

Posted 6-Mar-14 19:52pm

JoCodes

Updated 6-Mar-14 19:54pm

v2