REATE procedure employee_select @cust_num varchar(20),@ErrMsg varchar(100) out
as
select @ErrMsg = ''
if ((@cust_num is null ) or (@cust_num = ''))
begin
select a.cust_num,a.cust_name,a.sex,b.sbname as custkindname,a.opername,a.opertime ,
(select c.sbname from dictionary c where c.type_id=2 and c.hold1 = a.custkind_id and c.serial_id = a.rating_id ) as cust,a.up_opername
from employee a ,dictionary b where type_id=1 and b.serial_id=a.custkind_id
return 0
end
if not exists(select * from employee where cust_num=@cust_num)
begin
Set @ErrMsg='当前['+@cust_num+']信息不存在'
Return -1
end
else
begin
select a.cust_num,a.cust_name,a.sex,b.sbname as custkindname,a.opername,a.opertime ,
(select c.sbname from dictionary c where c.type_id=2 and c.hold1 = a.custkind_id and c.serial_id = a.rating_id ) as cust,a.up_opername
from employee a ,dictionary b where type_id=1 and b.serial_id=a.custkind_id and a.cust_num=@cust_num
end
return 0
GO
这是我的存储过程
在查询分析器里面房上参数有结果返回,
下面是在delphi的代码:procedure TEmployeeForm.select_employee;
var
Retu:Integer;
ErrMsg:String;
begin
try
ADOQry_add.Close;
ADOQry_add.SQL.Clear;
ADOQry_add.SQL.Add('declare @ErrMsg varchar(100);');
ADOQry_add.SQL.Add('declare @Retu int;');
ADOQry_add.SQL.Add('Exec @Retu=employee_select :Cust_Num,@ErrMsg out');
ADOQry_add.SQL.Add('select @Retu Retu,@ErrMsg ErrMsg');
ADOQry_add.Parameters.ParamByName('cust_num').Size:=20;
ADOQry_add.Parameters.ParamByName('cust_num').value:=cust_id;
ADOQry_add.Active:=True;
Retu:=ADOQry_add.FieldByName('Retu').AsInteger;
ErrMsg:=ADOQry_add.FieldByName('ErrMsg').AsString;
if Retu<>0 then
begin
MessageDlg('查询员工信息发生异常,整体处理失败!'+#13+ErrMsg,mtError,[mbAbort],0);
Exit;
ADOQry_add.Active:=False;
end
else
begin
showmsg('查询员工信息成功');
end;
except
on e:Exception do
begin
end;
end;end;
在delphi中运行老是提示找不到retu和errmsg这两个参数
而删除,修改,添加存储过程类似的写却没有此问题出现。
as
select @ErrMsg = ''
if ((@cust_num is null ) or (@cust_num = ''))
begin
select a.cust_num,a.cust_name,a.sex,b.sbname as custkindname,a.opername,a.opertime ,
(select c.sbname from dictionary c where c.type_id=2 and c.hold1 = a.custkind_id and c.serial_id = a.rating_id ) as cust,a.up_opername
from employee a ,dictionary b where type_id=1 and b.serial_id=a.custkind_id
return 0
end
if not exists(select * from employee where cust_num=@cust_num)
begin
Set @ErrMsg='当前['+@cust_num+']信息不存在'
Return -1
end
else
begin
select a.cust_num,a.cust_name,a.sex,b.sbname as custkindname,a.opername,a.opertime ,
(select c.sbname from dictionary c where c.type_id=2 and c.hold1 = a.custkind_id and c.serial_id = a.rating_id ) as cust,a.up_opername
from employee a ,dictionary b where type_id=1 and b.serial_id=a.custkind_id and a.cust_num=@cust_num
end
return 0
GO
这是我的存储过程
在查询分析器里面房上参数有结果返回,
下面是在delphi的代码:procedure TEmployeeForm.select_employee;
var
Retu:Integer;
ErrMsg:String;
begin
try
ADOQry_add.Close;
ADOQry_add.SQL.Clear;
ADOQry_add.SQL.Add('declare @ErrMsg varchar(100);');
ADOQry_add.SQL.Add('declare @Retu int;');
ADOQry_add.SQL.Add('Exec @Retu=employee_select :Cust_Num,@ErrMsg out');
ADOQry_add.SQL.Add('select @Retu Retu,@ErrMsg ErrMsg');
ADOQry_add.Parameters.ParamByName('cust_num').Size:=20;
ADOQry_add.Parameters.ParamByName('cust_num').value:=cust_id;
ADOQry_add.Active:=True;
Retu:=ADOQry_add.FieldByName('Retu').AsInteger;
ErrMsg:=ADOQry_add.FieldByName('ErrMsg').AsString;
if Retu<>0 then
begin
MessageDlg('查询员工信息发生异常,整体处理失败!'+#13+ErrMsg,mtError,[mbAbort],0);
Exit;
ADOQry_add.Active:=False;
end
else
begin
showmsg('查询员工信息成功');
end;
except
on e:Exception do
begin
end;
end;end;
在delphi中运行老是提示找不到retu和errmsg这两个参数
而删除,修改,添加存储过程类似的写却没有此问题出现。
存储过程里先declare @cust_num varchar(20),@ErrMsg varchar(100)
最后select @cust_num f1,@ErrMsg f2
去掉Retu,ErrMsg可以但是不能返回存储过程中的错误