我有以下存储过程:
alter procedure [usp_chkuseridpwd]
as
begin
declare
/*variable declaration */
@sql as varchar(max),
@tblname varchar(max),
@user_name varchar(max),
@user_password varchar(max),
@response varchar(max),
@count int,
@passwd as varchar(max),
@temppasswd as varchar(max)
set @sql = 'select COUNT(*) from user_master where USER_NAME like '+@user_name+' and pasword like '+@user_password+''
exec(@sql)
if(@count > 0)
begin
set @temppasswd=('select password from user_master where USER_NAME='+@user_name+'')
if (@temppasswd = @passwd)
set @response=('the password is incorrect !')
print @response //here I want to return response using message box
end
else
set @response = ('The user Id is not available')
print @response //here I want to return response using message box
end
在这里我有以下函数发送一些参数,如表名,用户名,密码(如果存储过程返回false,则应显示“无效用户名”,否则“用户是有效用户”)
public DataTable chkuseridpwd(ref string tname,ref string uname,ref string pwd)
{
try
{
if (cnn.State == System.Data.ConnectionState.Closed)
{
cnn.Open();
cmd = new SqlCommand("usp_chkuseridpwd", cnn);
cmd.CommandTimeout = 5000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tblname", tname);
cmd.Parameters.AddWithValue("@user_name", uname);
cmd.Parameters.AddWithValue("@user_password", pwd);
da = new SqlDataAdapter(cmd);
da.Fill(dtTbl);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cnn.Close();
}
return dtTbl;
}
2 回答
使用SQL Server的 RAISERROR 函数
根据您的代码使用
RAISERROR(@response)
您将在前端将其作为异常消息捕获
使用 output 参数执行此任务 . 以下是一些更多阅读的链接 .
http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output
https://www.toadworld.com/platforms/sql-server/w/wiki/10261.stored-procedures-output-parameters-return-values
https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx