从SQL Server存储过程返回一个字符串值 [英] Return a string Value from SQL Server stored procedure
问题描述
C#代码是
public void GetUserLoginName(字符串用户名)
{
SqlConnection sqlcon;
SqlCommand sqlcmd;
尝试
{
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)
{
}
}
和sql存储过程是
USE [COERP]
GO
/ ******对象:StoredProcedure [dbo]。[GetUserLoginName]脚本日期:03/07/2014 13:34:01 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER程序[dbo]。[GetUserLoginName](
@role varchar(max)out,
@Username varchar(max)
)
as
开始
设置@role =(从Newuser选择Name,其中Username = @ Username)
end
这段代码的输出是'@role'而不是@role里面的值!!
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 !!
推荐答案
你需要改变两件事:
1.Out应该在你的存储过程中添加到缺少的@role声明。
2.而不是只返回选择或设置@role的值(如果你要给它赋值)
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)
参考
如何到返回输出参数从 - 存储过程合ASPNET 一> [ ^ ]
获得-out参数 - 从-A-存储过程,在-ASP-网/ [ ^ ]
看看这里:创建程序 [< a href =http://technet.microsoft.com/en-us/library/ms187926.aspxtarget =_ blanktitle =New Window> ^ ]并转到 ℃。使用OUTPUT参数 部分;)
并这里 [ ^ ]你'将找到有关如何演示SP的信息;)
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 ;)
这篇关于从SQL Server存储过程返回一个字符串值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!