About Stored Procedure(28分)

请问如何用Tquery调用sp时,获取sp中的return值?

<a href="http://www.gislab.ecnu.edu.cn/delphibbs/DispQ.asp?LID=103297">看这里</a>

具体点呢？为什么不用TStoredProc?

一个是TStoredProc对参数的控制比较烦.其次就是在程序中调用的
sp及参数都是动态的.

come on guys!

各位大虾:
急啊!!!!!!!!!!
如何取Return值?
>>用TStoredProc取到的Return值不对,(在Delphi和ISQL中取到的不一样!)
再加50分

这样建立PROCEDURE:
create procedure getwhatsnew @newtype varchar(10)
as
if exists(select news from whatsnew where type=@newtype)
select isok='yes'
else
select isok='no'

QUERY这样写:
query1.sql.clear;
query1.sql.add('execute getwhatsnew ''all''');
query1.active:=true;
if query1.fieldbyname('isok').asstring='yes' then
showmessage('success!')
else
showmessage('failure!');

to cytown:
照你的做法,isok是什么东东.在Delphi中报找不到isok?

看见没有select isok='yes', isok就是判断是否正确的FIELDNAME.

如果isok不是表中已有字段,则还是报错.
:-?

如果是SP的话, 可以这样:
create procedure test as
declare @test int
select @test=@@error
execute sp_xxxx //你的SP语句
select @test=@@error
select isok=@test
go

然后如果QUERY1.EOF或QUERY1.FIELDBYNAME('ISOK')>0 就是错误了.

What's meaning.
I mean that I could not get the return value of the
Stored Procedure called by Tquery. What is I want to
say is That I have self define value to return in sp.

here->

query1.sql.clear;
query1.sql.add('execute sp_who');
query1.active:=true;

ok!

sun, 如果上面的都不行, 你把SP语句贴上来, 我再告诉你怎么做!

See This:
I want to call it by Tquery and gain the deffient return value.
But Everytime I got 0(in delphi) instead of 22(in ISQL).
>>
create procedure cp_pre_store2001(
@clinicno int,
@prescno int,
@mno int,
@ecount int,
@unit char(4),
@dosage int,
@usageno int,
@freq int,
@type int,
@result int output
)
as
declare @each int,@eachbase int,@countbase int,@rel int,@temp int
declare @serialno int
declare @batchno char(6)
declare @sno int
declare @continue int
declare @uprice money

begin transaction store
if not exists(
select PRE_STORE from M_STORE2001 holdlock
where M_NO=@mno
and PRE_STORE>=@ecount)
begin
rollback tran
select @result=10
end

update M_STORE2001 set PRE_STORE=PRE_STORE-@ecount where M_NO=@mno
if @@rowcount=0
begin
rollback tran
select @result=21
end

select PRE_STORE from M_BATCH2001 holdlock where M_NO=@mno

select @uprice=R_PRICE,@rel=RELATION from MPRICE
where M_NO=@mno
and BATCH_NO=@batchno
and BIG_UNIT=@unit

if @@rowcount=0
begin
rollback tran
select @result=22
end
else
select @countbase=@rel*@ecount

while @countbase>0
begin
select @continue=1
select @eachbase=PRE_STORE,@batchno=BATCH_NO from M_BATCH2001
where M_NO=@mno and PRE_STORE<>0 order by TOINVALID desc

if @eachbase>=@countbase
begin
select @each=@ecount
select @ecount=0
select @eachbase=@countbase
select @countbase=0
end
else
begin
if @type=0
begin
select @temp=@ecount-@each
select @ecount=@temp
select @temp=@countbase-@eachbase
select @countbase=@temp
end
else
select @continue=0
end

if @continue=1
begin
update M_BATCH2001 set PRE_STORE=PRE_STORE-@eachbase
where M_NO=@mno
and BATCH_NO=@batchno

if @@rowcount=0
begin
rollback tran
select @result=23
end

select @uprice=R_PRICE from MPRICE
where M_NO=@mno
and BATCH_NO=@batchno
and BIG_UNIT=@unit

if @@rowcount=0
begin
rollback tran
select @result=24
end


select @serialno=max(SERIAL_NO) from MSTOCK2001 holdlock
if @@rowcount=0
begin
select @serialno=0
end

insert into OUTPMEDICINES(CLINIC_NO,PRESC_NO,SNO,FEE_NO,M_NO,
BATCH_NO,UNIT,UNITPRICE,QUANTITY,DOSAGE,USAGE_NO,FREQUENCY)
select @clinicno,@prescno,@sno,FEE_NO,@mno,
@batchno,@unit,@uprice,@each,@dosage,@usageno,@freq
from MEDCINE
where M_NO=@mno
select @result=1
end /* end of continue */
end
commit transaction

>>>>
The calling in Delphi write like this:
>>>>
qryCp.Close;
qryCp.SQL.Text := 'declare @rlt int ';
qryCp.SQL.Text := qryCp.SQL.Text+'exec cp_pre_store'+frmPublicData.P_MSRC_CODE
+'('+edtClinic_No.Text+','+edtPresc_No.Text+','
+curM_No+','+edtQuantity.Text+',"'
+cbbxUnits.Text+'",'+Dosagestr+','
+Usagestr+','+FreqTypeStr+',1,@res=@rlt)';
qryCp.SQL.Add(' select @rlt as RT');
qryCp.Open;
rno := qryCp.FieldByname('RT').AsInteger;


>>
The rno is integer which I want to get the return value.

"+Usagestr+','+FreqTypeStr+',1,@res=@rlt)';"
此处：
+Usagestr+','+FreqTypeStr+',1,@res=@rlt OutPut)';
~~~~~~~

使过了,不行.

<a href='http://www.gislab.ecnu.edu.cn/delphibbs/dispq.asp?LID=132829'>再加100分,急啊!!!!!!!!!
</a>

qryCp.SQL.Text := qryCp.SQL.Text+'exec cp_pre_store'+frmPublicData.P_MSRC_CODE
+'('+edtClinic_No.Text+','+edtPresc_No.Text+','
+curM_No+','+edtQuantity.Text+',"'
+cbbxUnits.Text+'",'+Dosagestr+','
+Usagestr+','+FreqTypeStr+',1,@rlt=@result)';
=============这里错了!

to cytown :
那怎么写?
I see this method in
<a href='http://www.gislab.ecnu.edu.cn/delphibbs/dispq.asp?LID=103297'>here</a>