本来在sql里面已经测试过存储过程了,结果确实会将四个参数整合在一起,但是经过delphi调用之后却不行。delphi虽然也会生成合成后的字符,但是却将@dczrenyuan这个参数以空值传到数据库里面了,结果合成的字符就少了4个数。为什么会这样?再次说明,我在数据库里用exec代入参数的话确实会合成我想要的东西。这是delphi里面的代码
procedure TForm2.Button1Click(Sender: TObject);
begin
try
with ADOStoredProc1 do
begin
Close;
Parameters.Clear;
Parameters.Refresh;
Parameters.ParamByName('@re').Value:= Edit6.text;
Parameters.ParamByName('@riqi').Value:= DateTimeToStr(DateTimePicker1.DateTime);
Parameters.ParamByName('@czrenyuan').Value:= combobox1.text;
Parameters.ParamByName('@wuliaodaima').Value:= combobox2.text;
Parameters.ParamByName('@jcshuliang').Value:= Edit5.text;
prepared:=true;
execProc;
showmessage('结束');
end;
finally ADOStoredProc1.Free;
end;
end;然后是sql存储过程的代码
create proc jsj090412_jincang
(@riqi datetime, @czrenyuan varchar(10),@wuliaodaima varchar(10),@jcshuliang int,@re varchar(20))
as
begin
set nocount on;
declare @danhao varchar(25),@kucun int
declare @driqi varchar(6), @dczrenyuan varchar(4),@dwuliaodaima varchar(4),@djcshuliang varchar(5)
select @kucun=kucun from jsj090412_wuliao where @wuliaodaima=jsj090412_wuliao.code
set @kucun=@kucun+@jcshuliang
update jsj090412_wuliao
set kucun=@kucun
where jsj090412_wuliao.code=@wuliaodaima
set @dczrenyuan=(substring(@czrenyuan,7,4))
set @driqi = (convert(varchar(10),@riqi,12))
set @dwuliaodaima = (substring(@wuliaodaima,2,4))
set @djcshuliang = (convert(varchar(5),@jcshuliang))
set @danhao = @driqi+@dczrenyuan+@dwuliaodaima+@djcshuliang
insert into jsj090412_jincangjilu values(@danhao,@riqi,@czrenyuan,@re,@wuliaodaima,@jcshuliang)
end
go
procedure TForm2.Button1Click(Sender: TObject);
begin
try
with ADOStoredProc1 do
begin
Close;
Parameters.Clear;
Parameters.Refresh;
Parameters.ParamByName('@re').Value:= Edit6.text;
Parameters.ParamByName('@riqi').Value:= DateTimeToStr(DateTimePicker1.DateTime);
Parameters.ParamByName('@czrenyuan').Value:= combobox1.text;
Parameters.ParamByName('@wuliaodaima').Value:= combobox2.text;
Parameters.ParamByName('@jcshuliang').Value:= Edit5.text;
prepared:=true;
execProc;
showmessage('结束');
end;
finally ADOStoredProc1.Free;
end;
end;然后是sql存储过程的代码
create proc jsj090412_jincang
(@riqi datetime, @czrenyuan varchar(10),@wuliaodaima varchar(10),@jcshuliang int,@re varchar(20))
as
begin
set nocount on;
declare @danhao varchar(25),@kucun int
declare @driqi varchar(6), @dczrenyuan varchar(4),@dwuliaodaima varchar(4),@djcshuliang varchar(5)
select @kucun=kucun from jsj090412_wuliao where @wuliaodaima=jsj090412_wuliao.code
set @kucun=@kucun+@jcshuliang
update jsj090412_wuliao
set kucun=@kucun
where jsj090412_wuliao.code=@wuliaodaima
set @dczrenyuan=(substring(@czrenyuan,7,4))
set @driqi = (convert(varchar(10),@riqi,12))
set @dwuliaodaima = (substring(@wuliaodaima,2,4))
set @djcshuliang = (convert(varchar(5),@jcshuliang))
set @danhao = @driqi+@dczrenyuan+@dwuliaodaima+@djcshuliang
insert into jsj090412_jincangjilu values(@danhao,@riqi,@czrenyuan,@re,@wuliaodaima,@jcshuliang)
end
go
@czrenyuan测试时有10位数吗
set @czrenyuan ='12112aaaaa'
select @dczrenyuan=substring(@czrenyuan,5,4)
print @dczrenyuan
调试一下
Parameters.ParamByName('@riqi').Value:= DateTimeToStr(DateTimePicker1.DateTime);
Parameters.ParamByName('@czrenyuan').Value:= combobox1.text;
Parameters.ParamByName('@wuliaodaima').Value:= combobox2.text;
Parameters.ParamByName('@jcshuliang').Value:= Edit5.text;
@dczrenyuan的参数赋值在哪里?
set @driqi = (convert(varchar(10),@riqi,12))
set @dwuliaodaima = (substring(@wuliaodaima,2,4))
set @djcshuliang = (convert(varchar(5),@jcshuliang))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
那说明是你在ADO里传参数时错了,你把参数赋值方式改下:
字符串型的 trim()一下,日期型的formatdatetime('yyyy-mm-dd',now)一下,ADO参数顺序按存储过程的顺序来写。或都用ADOQUERY来执行存储过程看看:比如:
Sql.Text:='exec proc_2_total :ydw_,:ydwNo_,:ds_,:de_';
Parameters.ParamByName('ydw_').Value:=trim(ydw_);
Parameters.ParamByName('ydwNo_').Value:=trim(ydwNo_);
Parameters.ParamByName('ds_').Value:=FormatDateTime('yyyy-mm-dd',ds_);
Parameters.ParamByName('de_').Value:=FormatDateTime('yyyy-mm-dd',de_);