如何获取存储过程的返回值
我在程序中定义了一个数据访问的类
protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result;
Connection.Open();
SqlCommand command = BuildIntCommand( storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters[ "ReturnValue "].Value;
Connection.Close();
return result;
}
然后再外面调用
RunProcedure( "sp_Forums_InsertUser ", parameters, out numAffected);
我想问如何能取到 RAISERROR 产生得错误信息，即 "有同名用户存在 "这几个字
存储过程如下
CREATE PROCEDURE sp_Userinfo_InsertUser
@UserName varchar(100),
@lastip varchar(20),
@PassWord varchar(50),
@Email varchar(50),
@Question varchar(50),
@Answer varchar(50),
@birthday datetime,
@Pic varchar(50),
@PicWidth int,
@PicHeight int,
@Oicq varchar(50),
@Icq varchar(50),
@Msn varchar(50),
@HomePage varchar(100),
@Sign varchar(100),
@Sex char,
@Brief varchar(200),
@Favorite varchar(50),
@TrueName varchar(50),
@TrueID varchar(50),
@TruePhone varchar(50),
@UserID int OUTPUT
AS
DECLARE @CurrID int
-- see if the forum already exists
SELECT @CurrID =[ID]
FROM Userinfo
WHERE [userName] = @userName
-- if not, add it
IF @CurrID IS NULL
BEGIN
INSERT INTO userinfo
(lastip,username, [password], email, question, answer, pic, picwidth, pciheight, oicq, icq,msn,homepage, [sign], sex,brief, birthday,favorite,truename,truephone,trueid)
VALUES (@LastIP, @username, @password, @email,@question,@answer,@pic,@picwidth,@picheight,@oicq,@icq,@msn,@homepage,@sign,@sex,@brief,@birthday,@favorite,@truename,@truephone,@trueid)
SET @UserID = @@IDENTITY
IF @@ERROR > 0
BEGIN
RAISERROR ( '有同名用户存在 ', 16, 1)
RETURN 99
END
END
ELSE
BEGIN
SET @UserID = -1
END
GO
-----------------------------
1.如何在程序(VB.NET )中得到RAISERROR返回的字符,
2.如何得到RETURN返回的值99
分不够再加,请大家帮忙了!!!
[解决办法]
在查询分析器里运行，查看结果
如果只要得到一行记录，建议使用SqlDataReader
重要代码如下：
SqlDataReader dr;
SqlCommand command;
command=new SqlCommand(storedProcName,Connection)
command.CommandType=CommandType.StoredProcedure；
Connection.Open();
dr=command.ExecuteReader;
string str=dr.GetString(0);//取这一行中第一个为String类型的字段
dr.Close();
Connection.Close();
[解决办法]
不要意思写成C#了
Dim dr as new SqlDataReader
Dim command as new SqlCommand(storeProcName,Connection)
command.CommandType=CommandType.StoredProcedure
Connection.Open()
dr=command.ExecuteReader()
string str=dr.GetString(0) '取这一行中第一个为String类型的字段
dr.Close()
Connection.Close()
'好久没写VB了，可能有点小错，看编译器的错误提示修改
[解决办法]
........................
......................
................
Dim param As New SqlParameter( "@UserID ", SqlDbType.Int, 4)
param.Direction = ParameterDirection.Output
command.Parameters.Add(param)
command.ExecuteNonQuery();
Dim result As Integer
result= CInt(param.Value)
Connection.Close();
return result;
[解决办法]
存储过程的return值是这样获得
SqlParameter returnParameter=new SqlParameter( "returnValue ",SqlDbType.Int,4);
returnParameter.Direction=ParameterDirection.ReturnValue
把该参数加到SqlCommand对象即可
RAISERROR ( '有同名用户存在 ', 16, 1) 是不是在异常中捕获就不清楚了
------解决方案--------------------
才测试了下
RAISERROR ( '有同名用户存在 ', 16, 1)可以在异常中捕获
测试代码如下
存储过程:
create proc testRaisErr
as
raiserror( 'this is Err Test ',16,1)
return 99
go
C#代码:
System.Data.SqlClient.SqlCommand cmd=new System.Data.SqlClient.SqlCommand();
cmd.CommandText= "testRaisErr ";
cmd.CommandType=CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter p=new System.Data.SqlClient.SqlParameter( "returnValue ",SqlDbType.Int,4);
p.Direction=System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(p);
dataBase db=new dataBase();//自定义的执行SQL操作类
try
{
db.ExecuteNoQuery(cmd);
}
catch(System.Data.SqlClient.SqlException se)
{
Label1.Text=se.Message;
}
Label1.Text+= " <br> "+cmd.Parameters[0].Value.ToString();
成功得到了this is Err Test和99
