做了个存储过程,如下:
CREATE Proc XSPF
@SPSN varchar(8000), @JXSumJJ numeric(18,2) output, @DXSumJJ numeric(18,2) output
AS
Declare @SN varchar(50)
Declare @SPID uniqueidentifier
Declare @SPXZ varchar(50)
Declare @SPJJ numeric(18,2)
set @JXSumJJ = 0
set @DXSumJJ = 0
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in (@SPSN)OPEN t_cur
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
WHILE @@FETCH_STATUS = 0
BEGIN
if (@SPXZ = 'A') or (@SPXZ = '')
BEGIN
Set @JXSumJJ = @JXSumJJ + @SPJJ
END
ELSE if @SPXZ = 'B'
BEGIN
set @DXSumJJ = @DXSumJJ + @SPJJ
END
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
END
CLOSE t_cur
DEALLOCATE t_cur
RETURN @JXSumJJ
RETURN @DXSumJJ通过程序执行
var
s : string;
s1, s2 : Currency;
Begin
s1 := 000.00;
S2 := 000.00;
s := quotedstr('123') + ',' + quotedstr('234') + ',' + quotedstr('345');
ADOStoredProc.ProcedureName := 'XSPF';
ADOStoredProc.Parameters.Clear;
ADOStoredProc.Parameters.CreateParameter('@SPSN',ftString,pdInput,8000,s);
ADOStoredProc.Parameters.CreateParameter('@JXSumJJ', ftCurrency, pdOutput, 9, s1);
ADOStoredProc.Parameters.CreateParameter('@DXSumJJ', ftCurrency, pdOutput, 9, s1);
ADOStoredProc.ExecProc;
Edit1.Text := FloatToStr(s1);
Edit2.Text := FloatToStr(s2);
end;
返回的值都是0,后由在查询分析器中执行如下语句又可以:
Declare @SN varchar(50)
Declare @SPID uniqueidentifier
Declare @SPXZ varchar(50)
Declare @SPJJ numeric(18,2)
Declare @JXSumJJ numeric(18,2)
Declare @DXSumJJ numeric(18,2)
set @JXSumJJ = 0
set @DXSumJJ = 0
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in ('123','234','345')OPEN t_cur
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
WHILE @@FETCH_STATUS = 0
BEGIN
if (@SPXZ = 'A') or (@SPXZ = '')
BEGIN
Set @JXSumJJ = @JXSumJJ + @SPJJ
END
ELSE if @SPXZ = 'B'
BEGIN
set @DXSumJJ = @DXSumJJ + @SPJJ
END
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJEND
select @JXSumJJ,@DXSumJJ
CLOSE t_cur
DEALLOCATE t_cur
查了些贴子,也没有解决问题,严重郁闷中,请高手帮助
CREATE Proc XSPF
@SPSN varchar(8000), @JXSumJJ numeric(18,2) output, @DXSumJJ numeric(18,2) output
AS
Declare @SN varchar(50)
Declare @SPID uniqueidentifier
Declare @SPXZ varchar(50)
Declare @SPJJ numeric(18,2)
set @JXSumJJ = 0
set @DXSumJJ = 0
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in (@SPSN)OPEN t_cur
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
WHILE @@FETCH_STATUS = 0
BEGIN
if (@SPXZ = 'A') or (@SPXZ = '')
BEGIN
Set @JXSumJJ = @JXSumJJ + @SPJJ
END
ELSE if @SPXZ = 'B'
BEGIN
set @DXSumJJ = @DXSumJJ + @SPJJ
END
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
END
CLOSE t_cur
DEALLOCATE t_cur
RETURN @JXSumJJ
RETURN @DXSumJJ通过程序执行
var
s : string;
s1, s2 : Currency;
Begin
s1 := 000.00;
S2 := 000.00;
s := quotedstr('123') + ',' + quotedstr('234') + ',' + quotedstr('345');
ADOStoredProc.ProcedureName := 'XSPF';
ADOStoredProc.Parameters.Clear;
ADOStoredProc.Parameters.CreateParameter('@SPSN',ftString,pdInput,8000,s);
ADOStoredProc.Parameters.CreateParameter('@JXSumJJ', ftCurrency, pdOutput, 9, s1);
ADOStoredProc.Parameters.CreateParameter('@DXSumJJ', ftCurrency, pdOutput, 9, s1);
ADOStoredProc.ExecProc;
Edit1.Text := FloatToStr(s1);
Edit2.Text := FloatToStr(s2);
end;
返回的值都是0,后由在查询分析器中执行如下语句又可以:
Declare @SN varchar(50)
Declare @SPID uniqueidentifier
Declare @SPXZ varchar(50)
Declare @SPJJ numeric(18,2)
Declare @JXSumJJ numeric(18,2)
Declare @DXSumJJ numeric(18,2)
set @JXSumJJ = 0
set @DXSumJJ = 0
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in ('123','234','345')OPEN t_cur
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
WHILE @@FETCH_STATUS = 0
BEGIN
if (@SPXZ = 'A') or (@SPXZ = '')
BEGIN
Set @JXSumJJ = @JXSumJJ + @SPJJ
END
ELSE if @SPXZ = 'B'
BEGIN
set @DXSumJJ = @DXSumJJ + @SPJJ
END
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJEND
select @JXSumJJ,@DXSumJJ
CLOSE t_cur
DEALLOCATE t_cur
查了些贴子,也没有解决问题,严重郁闷中,请高手帮助
解决方案 »
- SQL命令大全-中英文对照
- 如何将一个表中的数据导入到几张表(3)(我想一条一条的导入) ???
- Microsoft SQL Server 2005 CTP的问题
- 合并行问题
- 朋友个网站,入库韩语的时候,就自动转成HTML实体了,怎么回事呢???
- 怎样将一个数据库完整复制到另一个sql服务器
- SQL 2000打开企业管理器报错“错误126:常规错误”
- 怎样写?表中记录数不确定,修改表中一部分记录的value字段值,同时将其余记录的value字段值改为0
- 怎么把ACCESS的内容倒入MS SQL2000呢?
- SQL批处理问题!!在线等!!
- 请高手修改 SQL 语句
- 求<MS SQLSERVER代码书写规范>
RETURN @JXSumJJ
RETURN @DXSumJJ
去掉试试.
刚又做了测试,用如下语句也是返回0的
declare @P1 float
set @P1=0.000000000000000e+000
declare @P2 float
set @P2=0.000000000000000e+000
exec XSPF '''123'',''234'',''345''', @P1 output, @P2 output
select @P1, @P2
exec
('
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in ('+@SPSN+')
')
...
...
s := ''''+ quotedstr('123') + ''',''' + quotedstr('234') + ''',''' + quotedstr('345') + '''';
...
但我后补充的那语语句就是从事件查看器中考出来的
declare @P1 float
set @P1=0.000000000000000e+000
declare @P2 float
set @P2=0.000000000000000e+000
exec XSPF '''123'',''234'',''345''', @P1 output, @P2 output
select @P1, @P2
该语句无法通过
where FSB_SN in (@SPSN)
因为@SPSN作为字符串参数传递进来根本就是一个字符串,而并不是楼主所期望的三个字符串:
'123','345','456'
where FSB_SN in (@SPSN)
效果相当于where FSB_SN in ('123,456,789')
而不是where FSB_SN in ('123','456','789').
if object_id('xspf') is not null
drop proc xspf
go
CREATE Proc XSPF
@SPSN varchar(8000),
@JXSumJJ numeric(18,2) output,
@DXSumJJ numeric(18,2) output
AS
declare @sql nvarchar(4000)
set @sql = N'
Declare @SN varchar(50)
Declare @SPID uniqueidentifier
Declare @SPXZ varchar(50)
Declare @SPJJ numeric(18,2)
set @JXSumJJ = 0
set @DXSumJJ = 0
DECLARE t_cur CURSOR FOR
select FSB_SN, FSB_SPID, FSB_SPXZ, FSB_SPJJ from KC_SBZL
where FSB_SN in (' + @SPSN + ')OPEN t_cur
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
WHILE @@FETCH_STATUS = 0
BEGIN
if (@SPXZ = ''A'') or (@SPXZ = '''')
BEGIN
Set @JXSumJJ = @JXSumJJ + @SPJJ
END
ELSE if @SPXZ = ''B''
BEGIN
set @DXSumJJ = @DXSumJJ + @SPJJ
END
FETCH NEXT FROM t_cur INTO @SN, @SPID, @SPXZ, @SPJJ
END
CLOSE t_cur
DEALLOCATE t_cur'
--print @sql
----执行动态构建的字符串
exec sp_executesql @sql,N'@JXSumJJ numeric(18,2) output,@DXSumJJ numeric(18,2) output',@JXSumJJ output,@DXSumJJ output
go
----测试
declare @P1 numeric(18,2)
set @P1=0.0
declare @P2 numeric(18,2)
set @P2=0.0
exec XSPF '''123'',''234'',''345''', @P1 output, @P2 output
select @P1, @P2--drop proc xspf