--这是表的脚本
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Incept_dcs_sl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Incept_dcs_sl]
GOCREATE TABLE [dbo].[Incept_dcs_sl] (
[rq] [datetime] NULL ,
[sj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[shsssll] [numeric](18, 3) NULL ,
[syssll] [numeric](18, 3) NULL ,
[yyssll] [numeric](18, 3) NULL ,
[tfssll] [numeric](18, 3) NULL ,
[shsljl] [numeric](18, 3) NULL ,
[syljl] [numeric](18, 3) NULL ,
[yyljl] [numeric](18, 3) NULL ,
[tfljl] [numeric](18, 3) NULL ,
[shssdpb] [numeric](18, 3) NULL ,
[yysdpb] [numeric](18, 3) NULL ,
[sysdpb] [numeric](18, 3) NULL ,
[tfsdpb] [numeric](18, 3) NULL ,
[sdts] [numeric](18, 3) NULL
) ON [PRIMARY]
GO--这是我写的存储过程
use singkong
if exists(select name from sysobjects where name='sl_EightCount')
drop procedure sl_EightCount
go
create procedure sl_EightCount
@datevalue datetime ,
@shsljl numeric output,
@yyljl numeric output,
@syljl numeric output,
@tfljl numeric output
as
declare @iHour int
set @iHour=datepart(hour,@datevalue)
declare @rq datetime
declare @sj char(10)
declare @max_shsljl numeric --最大时间上对应的值
declare @max_yyljl numeric
declare @max_syljl numeric
declare @max_tfljl numeric
declare @min_shsljl numeric --最小时间对应的值
declare @min_yyljl numeric
declare @min_syljl numeric
declare @min_tfljl numeric
declare @value_shsljl numeric --最大时间对应值-最小时间对应值
declare @value_yyljl numeric
declare @value_syljl numeric
declare @value_tfljl numeric
declare @limit_sl_shs numeric --出现清0时的极限值
declare @limit_sl_yy numeric
declare @limit_sl_sy numeric
declare @limit_sl_tf numeric
if @iHour>=0 and @iHour<8
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=0 and datepart(hour,sj)<8 )
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue-1,110) and sj in (select max(sj) from Incept_dcs_sl)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
end
if @iHour>=8 and @iHour<16
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=8 and datepart(hour,sj)<16)
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)<8)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
end
if @iHour>=16 and @iHour<24
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=16 and datepart(hour,sj)<24)
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)<16)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
endif @value_shsljl<0
begin
select @limit_sl_shs=sl_shs from MaxValue
set @value_shsljl=@max_shsljl+@limit_sl_shs-@min_shsljl
end
if @value_yyljl<0
begin
select @limit_sl_yy=sl_yy from MaxValue
set @value_yyljl=@max_yyljl+@limit_sl_yy-@min_yyljl
end
if @value_syljl<0
begin
select @limit_sl_sy=sl_sy from MaxValue
set @value_syljl=@max_syljl+@limit_sl_sy-@min_syljl
end
if @value_tfljl<0
begin
select @limit_sl_tf=sl_tf from MaxValue
set @value_tfljl=@max_tfljl+@limit_sl_tf-@min_tfljl
end
go--这是用VB调用存储过程的代码Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prmDate As New ADODB.Parameter
Dim prmShsljl As New ADODB.Parameter
Dim prmYyljl As New ADODB.Parameter
Dim prmSyljl As New ADODB.Parameter
Dim prmTfljl As New ADODB.Parameter
Dim Return_shsljl As Double
Dim Return_yyljl As Double
Dim Return_syljl As Double
Dim Return_tfljl As Double Set prmDate = cmd.CreateParameter("@datevalue", adDate, adParamInput, , Now)
cmd.Parameters.Append prmDate
Set prmShsljl = cmd.CreateParameter("@shsljl", adDouble, adParamOutput)
cmd.Parameters.Append prmShsljl
Set prmYyljl = cmd.CreateParameter("@yyljl", adDouble, adParamOutput)
cmd.Parameters.Append prmYyljl
Set prmSyljl = cmd.CreateParameter("@syljl", adDouble, adParamOutput)
cmd.Parameters.Append prmSyljl
Set prmTfljl = cmd.CreateParameter("@tfljl", adDouble, adParamOutput)
cmd.Parameters.Append prmTfljl
cmd.CommandText = "sl_EightCount"
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = cn
cmd.Execute
Return_shsljl = cmd.Parameters("@shsljl")
Return_yyljl = cmd.Parameters("@yyljl")
Return_syljl = cmd.Parameters("@syljl")
Return_tfljl = cmd.Parameters("@tfljl")
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Incept_dcs_sl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Incept_dcs_sl]
GOCREATE TABLE [dbo].[Incept_dcs_sl] (
[rq] [datetime] NULL ,
[sj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[shsssll] [numeric](18, 3) NULL ,
[syssll] [numeric](18, 3) NULL ,
[yyssll] [numeric](18, 3) NULL ,
[tfssll] [numeric](18, 3) NULL ,
[shsljl] [numeric](18, 3) NULL ,
[syljl] [numeric](18, 3) NULL ,
[yyljl] [numeric](18, 3) NULL ,
[tfljl] [numeric](18, 3) NULL ,
[shssdpb] [numeric](18, 3) NULL ,
[yysdpb] [numeric](18, 3) NULL ,
[sysdpb] [numeric](18, 3) NULL ,
[tfsdpb] [numeric](18, 3) NULL ,
[sdts] [numeric](18, 3) NULL
) ON [PRIMARY]
GO--这是我写的存储过程
use singkong
if exists(select name from sysobjects where name='sl_EightCount')
drop procedure sl_EightCount
go
create procedure sl_EightCount
@datevalue datetime ,
@shsljl numeric output,
@yyljl numeric output,
@syljl numeric output,
@tfljl numeric output
as
declare @iHour int
set @iHour=datepart(hour,@datevalue)
declare @rq datetime
declare @sj char(10)
declare @max_shsljl numeric --最大时间上对应的值
declare @max_yyljl numeric
declare @max_syljl numeric
declare @max_tfljl numeric
declare @min_shsljl numeric --最小时间对应的值
declare @min_yyljl numeric
declare @min_syljl numeric
declare @min_tfljl numeric
declare @value_shsljl numeric --最大时间对应值-最小时间对应值
declare @value_yyljl numeric
declare @value_syljl numeric
declare @value_tfljl numeric
declare @limit_sl_shs numeric --出现清0时的极限值
declare @limit_sl_yy numeric
declare @limit_sl_sy numeric
declare @limit_sl_tf numeric
if @iHour>=0 and @iHour<8
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=0 and datepart(hour,sj)<8 )
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue-1,110) and sj in (select max(sj) from Incept_dcs_sl)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
end
if @iHour>=8 and @iHour<16
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=8 and datepart(hour,sj)<16)
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)<8)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
end
if @iHour>=16 and @iHour<24
begin
select @rq=rq,@sj=sj,@max_shsljl=shsljl,@max_yyljl=yyljl,@max_syljl=syljl,@max_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)>=16 and datepart(hour,sj)<24)
select @rq=rq,@sj=sj,@min_shsljl=shsljl,@min_yyljl=yyljl,@min_syljl=syljl,@min_tfljl=tfljl from Incept_dcs_sl where rq=convert(datetime,@datevalue,110) and sj in (select max(sj) from Incept_dcs_sl where datepart(hour,sj)<16)
set @value_shsljl=@max_shsljl-@min_shsljl
set @value_yyljl=@max_yyljl-@min_yyljl
set @value_syljl=@max_syljl-@min_syljl
set @value_tfljl=@max_tfljl-@min_tfljl
endif @value_shsljl<0
begin
select @limit_sl_shs=sl_shs from MaxValue
set @value_shsljl=@max_shsljl+@limit_sl_shs-@min_shsljl
end
if @value_yyljl<0
begin
select @limit_sl_yy=sl_yy from MaxValue
set @value_yyljl=@max_yyljl+@limit_sl_yy-@min_yyljl
end
if @value_syljl<0
begin
select @limit_sl_sy=sl_sy from MaxValue
set @value_syljl=@max_syljl+@limit_sl_sy-@min_syljl
end
if @value_tfljl<0
begin
select @limit_sl_tf=sl_tf from MaxValue
set @value_tfljl=@max_tfljl+@limit_sl_tf-@min_tfljl
end
go--这是用VB调用存储过程的代码Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prmDate As New ADODB.Parameter
Dim prmShsljl As New ADODB.Parameter
Dim prmYyljl As New ADODB.Parameter
Dim prmSyljl As New ADODB.Parameter
Dim prmTfljl As New ADODB.Parameter
Dim Return_shsljl As Double
Dim Return_yyljl As Double
Dim Return_syljl As Double
Dim Return_tfljl As Double Set prmDate = cmd.CreateParameter("@datevalue", adDate, adParamInput, , Now)
cmd.Parameters.Append prmDate
Set prmShsljl = cmd.CreateParameter("@shsljl", adDouble, adParamOutput)
cmd.Parameters.Append prmShsljl
Set prmYyljl = cmd.CreateParameter("@yyljl", adDouble, adParamOutput)
cmd.Parameters.Append prmYyljl
Set prmSyljl = cmd.CreateParameter("@syljl", adDouble, adParamOutput)
cmd.Parameters.Append prmSyljl
Set prmTfljl = cmd.CreateParameter("@tfljl", adDouble, adParamOutput)
cmd.Parameters.Append prmTfljl
cmd.CommandText = "sl_EightCount"
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = cn
cmd.Execute
Return_shsljl = cmd.Parameters("@shsljl")
Return_yyljl = cmd.Parameters("@yyljl")
Return_syljl = cmd.Parameters("@syljl")
Return_tfljl = cmd.Parameters("@tfljl")
首先你应当添加一个返回参数,用于返回存储过程是否正确返回,
然后再可能出错的地方执行完后判断是否出错;另外你没有判断是否有符合条件的结果存在就复值都有可能导致你收不到返回值。最后vb和sql的数据类型不同,你在定义表结构的时候尽量向vb能够明确确认的数据类型!祝你好运!
改为
Set prmDate = cmd.CreateParameter("@datevalue", adVarChar, adParamInput,30 , Now)试试
accessQQ.com 2004-3-4 ---------------------------------
ALTER PROCEDURE sp_2
@p int output,
@p1 int,
@p2 intas
set @p = @p1+@p2
return 99
---------------------------------Function returnP() Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CONN
'@return_value"这个参数在输入参数的最前面,名称是固定的
cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue)'输入和输出的参数顺序同存储过程的参数顺序 cmd.Parameters.Append cmd.CreateParameter("@p", adInteger, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@p1", adInteger, adParamInput,,1)
cmd.Parameters.Append cmd.CreateParameter("@p2", adInteger, adParamInput,,2) cmd.CommandText = "sp_2"
cmd.CommandType = adCmdStoredProc cmd.Execute Dim i As Integer For i = 0 To cmd.Parameters.Count - 1
Debug.Print cmd.Parameters(i).Name & "=" & cmd.Parameters(i).Value
Next iSet cmd.ActiveConnection = Nothing
Set cmd = NothingEnd Function'----------测试---------? returnP()
@return_value=99
@p=3
@p2=2
@p1=1