| |
| Q : |
一个储存过程如何在VB里返回记录集？ |
| HashCode: |
CSDNMxuqi92188CSDNM、leimin、CSDNMtj_dns1134346 |
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'io_InOutDtl'
AND type = 'P')
DROP PROCEDURE io_InOutDtl
GO
CREATE PROCEDURE io_InOutDtl
@dDate1 datetime='2002-7-15',
@dDate2 datetime=null,
@cInvCode varchar(20)=null
AS
declare @fFirstQuantity float
declare @fFirstMoney float
declare @dDate3 datetime
declare @dDate4 datetime
declare @sql nvarchar(4000)
declare @wsql nvarchar(4000)
declare @fQuantity float
declare @fMoney float
--//初始化变量
select @fQuantity=0
select @fMoney=0
select @fFirstQuantity=0
select @fFirstMoney=0
--//
--//先得到过滤条件
if @dDate1 is not null
select @wsql=' and dDate>= '''+convert(nvarchar(100),@dDate1,121)+''' '
if @dDate2 is not null
select @wsql=@wsql+' and dDate<= '''+convert(nvarchar(100),@dDate2,121)+''' '
if @cInvCode is not null
select @wsql=@wsql+' and cVouchType= '''+@cInvCode+''' '
--//
IF object_id('tempdb.dbo.#tintmp') IS NOT NULL
DROP TABLE [dbo].[#tintmp]
IF object_id('tempdb.dbo.#tin') IS NOT NULL
DROP TABLE [dbo].[#tin]
CREATE TABLE #tintmp (
--AutoID int IDENTITY (1,1),
cVouchID Varchar(14),
cVouchType Varchar(2),
dDate Datetime,
cCVCode Nvarchar(50) DEFAULT '',
cInvCode Varchar(20),
cBatch Varchar(20),
fPrice float DEFAULT 0,
fFirstQuantity float DEFAULT 0,
fFirstMoney float DEFAULT 0,
fInitQuantity float DEFAULT 0,
fInitMoney float DEFAULT 0,
fInQuantity float DEFAULT 0,
fInMoney float DEFAULT 0,
fOutQuantity float DEFAULT 0,
fOutMoney float DEFAULT 0,
fRemainQuantity float DEFAULT 0,
fRemainMoney float DEFAULT 0
)
CREATE TABLE #tin (
AutoID int IDENTITY (1,1),
cVouchID Varchar(14),
cVouchType Varchar(2),
dDate Datetime,
cCVCode Nvarchar(50) DEFAULT '',
cInvCode Varchar(20),
cBatch Varchar(20),
fPrice float DEFAULT 0,
fFirstQuantity float DEFAULT 0,
fFirstMoney float DEFAULT 0,
fInitQuantity float DEFAULT 0,
fInitMoney float DEFAULT 0,
fInQuantity float DEFAULT 0,
fInMoney float DEFAULT 0,
fOutQuantity float DEFAULT 0,
fOutMoney float DEFAULT 0,
fRemainQuantity float DEFAULT 0,
fRemainMoney float DEFAULT 0
)
--//合并单据
Insert into #tintmp(cVouchID,cVouchType,dDate,cInvCode,cBatch,fPrice,fInitQuantity,fInitMoney)
Select cVouchID,cVouchType,dCheckDate as dDate,cInvCode,cBatch,fPrice
,fQuantity as fInitQuantity,fTotalMoney as fInitMoney
from InitStockVouch iv,InitStockVouchDetail ivd
where iv.cVouchID=ivd.MainID
insert into #tintmp(cVouchID,cVouchType,dDate,cInvCode,cBatch,fPrice,fInQuantity,fInMoney)
Select cVouchID,cVouchType,dVouchDate as dDate,cInvCode,cBatch,fPrice,fQuantity,pvd.fNotaxMoney
from PurInitVouch pv,PurInitVouchDetail pvd
where pv.cVouchID=pvd.MainID
insert into #tintmp(cVouchID,cVouchType,dDate,cInvCode,cBatch,fPrice,fOutQuantity,fOutMoney)
Select cVouchID,cVouchType,dVouchDate as dDate,cInvCode,cBatch,fPrice,fQuantity,fTotalMoney
from SaleInitVouch sv,SaleInitVouchDetail svd
where sv.cVouchID=svd.MainID
--//
--//搞上期结余
if DAY(@dDate1)<(select iEnd from AccPeriod where iMonth=MONTH(@dDate1))
begin
select @dDate3='2002-'+(select cast(iMonth as varchar(10))+'-'+cast(iEnd as varchar(10)) from AccPeriod where iMonth=(MONTH(@dDate1)-1))
select @dDate4=@dDate1
end
else
begin
select @dDate3='2002-'+(select cast(iMonth as varchar(10))+'-'+cast(iEnd as varchar(10)) from AccPeriod where iMonth=MONTH(@dDate1))
select @dDate4=@dDate1
end
select @fQuantity=fRemain from ct_BatchMonth where iMonth=MONTH(@dDate3) and cInvCode=''+@cInvCode+''
if @fQuantity is not null
select @fFirstQuantity=@fFirstQuantity+@fQuantity
select @fMoney=@fQuantity*fCostPrice from ct_CostPriceMonth where iMonth=MONTH(@dDate3)
if @fMoney is not null
select @fFirstMoney=@fFirstMoney+@fMoney
select @fQuantity=@fFirstQuantity+sum(fInitQuantity)+sum(fInQuantity)-sum(fOutQuantity)
from #tintmp
where dDate>@dDate3 and dDate<@dDate4 and cInvCode=''+@
---------------------------------------------------------------
set rs=con.execute "Exec io_InOutDtl "
---------------------------------------------------------------
pls try:
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "io_InOutDtl "
cmd.CommandType = adCmdStoredProc
'往command 对象中加参数
'定义存储过程有直接返回值，并且是个整数，省缺值是4
CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
'定义一个字符型输入参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@dDate1 ", adDBTimeStamp, adParaminput, , dDate1)
'定义一个整型输入参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@dDate2 ", adDBTimeStamp, adParamInput, , dDate2)
'定义一个整型输出参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@cInvCode ", advarChar, adParamInput, 20,oInvCode)
'运行存储过程，并得到返回记录集
Set adoRS = CmdSP.Execute
---------------------------------------------------------------
可以,不过需要斑竹帮忙,删除你的帖子,你的可用分就回去了,但是你的信誉分就减少5分.
需要帮忙的话可以发短消息或者在这里回复.
|
|