1\ dim cn as new adodb.connection dim rs as new adodb.recordset cn.provider= "SQLOLEDB " cn.open "Driver={SQL Server};Server=Develop;UID=SA;PWD=;Database=DB " set rs=cn.execute( "select * from sysobjects where id=object_id('"&+"要判断是否存在的对象名"+&"') ") if not rs.eof then .....2\执行的语句修改下就差不多了
SQL代码: if object_id('dbname.dbo.proc_name','p')>0 --判断是否存在 drop proc dbname.dbo.proc_name --如果存在先删除 go create proc dbname.dbo.proc_name --创建过程 as ...
能不能用VB代码通过文本文件内容(内容是创建存储过程的代码)创建存储过程?如果能如何写VB代码?
如何写VB代码以下内容让其他自动创建存储过程?CREATE PROCEDURE SX_bSx_InOutSumList_1 @StartDate datetime, @EndDate datetime AS begin tran --删除旧数据 delete from BB_DepotKCList --插入初始数据和库存 --INSERT INTO BB_DepotKCList(Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney) -- SELECT Gy_DepotKc.Productid,Gy_DepotKc.ProductName,Gy_DepotKc.ProductSpecs,Gy_DepotKc.UnitName,Gy_DepotKc.DepotName, -- sum(Gy_DepotKc.InitNum) as InitNum,Sum(Gy_DepotKc.InitMoney) AS InitMoney -- From Gy_DepotKc -- GROUP BY Gy_DepotKc.Productid,Gy_DepotKc.ProductName,Gy_DepotKc.ProductSpecs,Gy_DepotKc.UnitName,Gy_DepotKc.DepotName -- order by Gy_DepotKc.Productid --插入上次盘点数据 INSERT INTO BB_DepotKCList(BillName,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney) SELECT '1上期结存',CK_V_PD_CheckBill.ClientID,CK_V_PD_CheckBill.ClientName,CK_V_PD_CheckBill.Productid,CK_V_PD_CheckBill.ProductName,CK_V_PD_CheckBill.ProductSpecs, CK_V_PD_CheckBill.MachiningSort, CK_V_PD_CheckBill. DepotName,SUM(Quantity) AS Number,sum(CK_V_PD_CheckBill.Weight) as Quantity From CK_V_PD_CheckBill where CK_V_PD_CheckBill.IsSh='1' and CK_V_PD_CheckBill.YnLast='Y' GROUP BY CK_V_PD_CheckBill.ClientID, CK_V_PD_CheckBill.ClientName,CK_V_PD_CheckBill.Productid,CK_V_PD_CheckBill.Productname,CK_V_PD_CheckBill.ProductSpecs, CK_V_PD_CheckBill.MachiningSort, CK_V_PD_CheckBill. DepotName order by CK_V_PD_CheckBill.Productid --插入上期入库数据 INSERT INTO BB_DepotKCList(BillName,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney) SELECT '1上期结存',Xs_V_InDepotBill.SaleDate,Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName, Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName, sum(Xs_V_InDepotBill.Quantity) as Quantity,Sum(Xs_V_InDepotBill.Money) AS Money From Xs_V_InDepotBill where Xs_V_InDepotBill.issh='1' and SaleDate < @StartDate and Xs_V_InDepotBill.CheckDate is null GROUP BY Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName,Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.SaleDate order by Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.Productid--插入上期退库数据 INSERT INTO BB_DepotKCList(Billname,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney) SELECT '1上期结存', Xs_V_OutDepotBill.SaleDate, Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName, Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.DepotName, -sum(Xs_V_OutDepotBill.Quantity) as Quantity,-Sum(Xs_V_OutDepotBill.Money) AS Money From Xs_V_OutDepotBill where Xs_V_OutDepotBill.issh='1' and SaleDate< @StartDate and Xs_V_OutDepotBill.CheckDate is null GROUP BY Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName,Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.DepotName,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.SaleDate order by Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.Productid--插入上期发货数据 INSERT INTO BB_DepotKCList(Billname,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney) SELECT '1上期结存',Xs_V_SaleBill.SaleDate,Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_SaleBill.Productid,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort,Xs_V_SaleBill.DepotName, -sum(Xs_V_SaleBill.Quantity) as Quantity,-Sum(Xs_V_SaleBill.Money) AS Money From Xs_V_SaleBill where Xs_V_SaleBill.issh='1' and SaleDate <@StartDate and Xs_V_SaleBill.CheckDate is null GROUP BY Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_saleBill.Productid,Xs_V_SaleBill.DepotName,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort, Xs_V_SaleBill.SaleDate order by Xs_V_SaleBill.Clientid,Xs_V_SaleBill.Productid--插入本期入库数据 INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InQuantity,InMoney) SELECT Xs_V_InDepotBill.SaleDate,Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName, Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName, sum(Xs_V_InDepotBill.Quantity) as Quantity,Sum(Xs_V_InDepotBill.Money) AS Money From Xs_V_InDepotBill where Xs_V_InDepotBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_InDepotBill.CheckDate is null GROUP BY Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName,Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.SaleDate order by Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.Productid--插入本期退库数据 INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,bInQuantity,bInMoney) SELECT Xs_V_OutDepotBill.SaleDate,Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName, Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.DepotName, sum(Xs_V_OutDepotBill.Quantity) as Quantity,Sum(Xs_V_OutDepotBill.Money) AS Money From Xs_V_OutDepotBill where Xs_V_OutDepotBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_OutDepotBill.CheckDate is null GROUP BY Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName,Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.DepotName,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.SaleDate order by Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.Productid--插入本期发货数据 INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,OutQuantity,OutMoney) SELECT Xs_V_SaleBill.SaleDate,Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_SaleBill.Productid,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort,Xs_V_SaleBill.DepotName, sum(Xs_V_SaleBill.Quantity) as Quantity,Sum(Xs_V_SaleBill.Money) AS Money From Xs_V_SaleBill where Xs_V_SaleBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_SaleBill.CheckDate is null GROUP BY Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_saleBill.Productid,Xs_V_SaleBill.DepotName,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort, Xs_V_SaleBill.SaleDate order by Xs_V_SaleBill.Clientid,Xs_V_SaleBill.Productid --插入退货数据 --INSERT INTO BB_DepotKCList(Productid,Productname,ProductSpecs,UnitName,DepotName,bOutQuantity,bOutMoney) -- SELECT Xs_V_bSaleBill.Productid,Xs_V_bSaleBill.ProductName,Xs_V_bSaleBill.ProductSpecs,Xs_V_bSaleBill.MachiningSort,Xs_V_bSaleBill.DepotName, -- sum(Xs_V_bSaleBill.Quantity) as Quantity,Sum(Xs_V_bSaleBill.Money) AS Money -- From Xs_V_bSaleBill -- where Xs_V_bSaleBill.issh='1' -- GROUP BY Xs_V_bSaleBill.Productid,Xs_V_bSaleBill.DepotName,Xs_V_bSaleBill.ProductName,Xs_V_bSaleBill.ProductSpecs,Xs_V_bSaleBill.MachiningSort -- order by Xs_V_bSaleBill.Productid Update BB_DepotKCList set Billname=convert(char(10),SaleDate,120) where Billname='' commit GO
drop.....
create .........
dim cn as new adodb.connection
dim rs as new adodb.recordset cn.provider= "SQLOLEDB "
cn.open "Driver={SQL Server};Server=Develop;UID=SA;PWD=;Database=DB " set rs=cn.execute( "select * from sysobjects where id=object_id('"&+"要判断是否存在的对象名"+&"') ")
if not rs.eof then
.....2\执行的语句修改下就差不多了
if object_id('dbname.dbo.proc_name','p')>0 --判断是否存在
drop proc dbname.dbo.proc_name --如果存在先删除
go
create proc dbname.dbo.proc_name --创建过程
as ...
如何写VB代码以下内容让其他自动创建存储过程?CREATE PROCEDURE SX_bSx_InOutSumList_1
@StartDate datetime,
@EndDate datetime
AS
begin tran
--删除旧数据
delete from BB_DepotKCList
--插入初始数据和库存
--INSERT INTO BB_DepotKCList(Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney)
-- SELECT Gy_DepotKc.Productid,Gy_DepotKc.ProductName,Gy_DepotKc.ProductSpecs,Gy_DepotKc.UnitName,Gy_DepotKc.DepotName,
-- sum(Gy_DepotKc.InitNum) as InitNum,Sum(Gy_DepotKc.InitMoney) AS InitMoney
-- From Gy_DepotKc
-- GROUP BY Gy_DepotKc.Productid,Gy_DepotKc.ProductName,Gy_DepotKc.ProductSpecs,Gy_DepotKc.UnitName,Gy_DepotKc.DepotName
-- order by Gy_DepotKc.Productid
--插入上次盘点数据
INSERT INTO BB_DepotKCList(BillName,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney)
SELECT '1上期结存',CK_V_PD_CheckBill.ClientID,CK_V_PD_CheckBill.ClientName,CK_V_PD_CheckBill.Productid,CK_V_PD_CheckBill.ProductName,CK_V_PD_CheckBill.ProductSpecs,
CK_V_PD_CheckBill.MachiningSort, CK_V_PD_CheckBill. DepotName,SUM(Quantity) AS Number,sum(CK_V_PD_CheckBill.Weight) as Quantity
From CK_V_PD_CheckBill
where CK_V_PD_CheckBill.IsSh='1' and CK_V_PD_CheckBill.YnLast='Y'
GROUP BY CK_V_PD_CheckBill.ClientID, CK_V_PD_CheckBill.ClientName,CK_V_PD_CheckBill.Productid,CK_V_PD_CheckBill.Productname,CK_V_PD_CheckBill.ProductSpecs,
CK_V_PD_CheckBill.MachiningSort, CK_V_PD_CheckBill. DepotName
order by CK_V_PD_CheckBill.Productid
--插入上期入库数据
INSERT INTO BB_DepotKCList(BillName,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney)
SELECT '1上期结存',Xs_V_InDepotBill.SaleDate,Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName, Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,
sum(Xs_V_InDepotBill.Quantity) as Quantity,Sum(Xs_V_InDepotBill.Money) AS Money
From Xs_V_InDepotBill
where Xs_V_InDepotBill.issh='1' and SaleDate < @StartDate and Xs_V_InDepotBill.CheckDate is null
GROUP BY Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName,Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.SaleDate
order by Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.Productid--插入上期退库数据
INSERT INTO BB_DepotKCList(Billname,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney)
SELECT '1上期结存', Xs_V_OutDepotBill.SaleDate, Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName, Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.DepotName,
-sum(Xs_V_OutDepotBill.Quantity) as Quantity,-Sum(Xs_V_OutDepotBill.Money) AS Money
From Xs_V_OutDepotBill
where Xs_V_OutDepotBill.issh='1' and SaleDate< @StartDate and Xs_V_OutDepotBill.CheckDate is null
GROUP BY Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName,Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.DepotName,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.SaleDate
order by Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.Productid--插入上期发货数据
INSERT INTO BB_DepotKCList(Billname,Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InitNum,Initmoney)
SELECT '1上期结存',Xs_V_SaleBill.SaleDate,Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_SaleBill.Productid,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort,Xs_V_SaleBill.DepotName,
-sum(Xs_V_SaleBill.Quantity) as Quantity,-Sum(Xs_V_SaleBill.Money) AS Money
From Xs_V_SaleBill
where Xs_V_SaleBill.issh='1' and SaleDate <@StartDate and Xs_V_SaleBill.CheckDate is null
GROUP BY Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_saleBill.Productid,Xs_V_SaleBill.DepotName,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort, Xs_V_SaleBill.SaleDate
order by Xs_V_SaleBill.Clientid,Xs_V_SaleBill.Productid--插入本期入库数据
INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,InQuantity,InMoney)
SELECT Xs_V_InDepotBill.SaleDate,Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName, Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,
sum(Xs_V_InDepotBill.Quantity) as Quantity,Sum(Xs_V_InDepotBill.Money) AS Money
From Xs_V_InDepotBill
where Xs_V_InDepotBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_InDepotBill.CheckDate is null
GROUP BY Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.ClientName,Xs_V_InDepotBill.Productid,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.ProductName,Xs_V_InDepotBill.ProductSpecs,Xs_V_InDepotBill.MachiningSort,Xs_V_InDepotBill.DepotName,Xs_V_InDepotBill.SaleDate
order by Xs_V_InDepotBill.Clientid,Xs_V_InDepotBill.Productid--插入本期退库数据
INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,bInQuantity,bInMoney)
SELECT Xs_V_OutDepotBill.SaleDate,Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName, Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.DepotName,
sum(Xs_V_OutDepotBill.Quantity) as Quantity,Sum(Xs_V_OutDepotBill.Money) AS Money
From Xs_V_OutDepotBill
where Xs_V_OutDepotBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_OutDepotBill.CheckDate is null
GROUP BY Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.ClientName,Xs_V_OutDepotBill.Productid,Xs_V_OutDepotBill.DepotName,Xs_V_OutDepotBill.ProductName,Xs_V_OutDepotBill.ProductSpecs,Xs_V_OutDepotBill.MachiningSort,Xs_V_OutDepotBill.SaleDate
order by Xs_V_OutDepotBill.Clientid,Xs_V_OutDepotBill.Productid--插入本期发货数据
INSERT INTO BB_DepotKCList(Saledate,ClientID,ClientName,Productid,Productname,ProductSpecs,UnitName,DepotName,OutQuantity,OutMoney)
SELECT Xs_V_SaleBill.SaleDate,Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_SaleBill.Productid,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort,Xs_V_SaleBill.DepotName,
sum(Xs_V_SaleBill.Quantity) as Quantity,Sum(Xs_V_SaleBill.Money) AS Money
From Xs_V_SaleBill
where Xs_V_SaleBill.issh='1' and SaleDate between @StartDate and @EndDate and Xs_V_SaleBill.CheckDate is null
GROUP BY Xs_V_SaleBill.Clientid,Xs_V_SaleBill.ClientName, Xs_V_saleBill.Productid,Xs_V_SaleBill.DepotName,Xs_V_SaleBill.ProductName,Xs_V_SaleBill.ProductSpecs,Xs_V_SaleBill.MachiningSort, Xs_V_SaleBill.SaleDate
order by Xs_V_SaleBill.Clientid,Xs_V_SaleBill.Productid
--插入退货数据
--INSERT INTO BB_DepotKCList(Productid,Productname,ProductSpecs,UnitName,DepotName,bOutQuantity,bOutMoney)
-- SELECT Xs_V_bSaleBill.Productid,Xs_V_bSaleBill.ProductName,Xs_V_bSaleBill.ProductSpecs,Xs_V_bSaleBill.MachiningSort,Xs_V_bSaleBill.DepotName,
-- sum(Xs_V_bSaleBill.Quantity) as Quantity,Sum(Xs_V_bSaleBill.Money) AS Money
-- From Xs_V_bSaleBill
-- where Xs_V_bSaleBill.issh='1'
-- GROUP BY Xs_V_bSaleBill.Productid,Xs_V_bSaleBill.DepotName,Xs_V_bSaleBill.ProductName,Xs_V_bSaleBill.ProductSpecs,Xs_V_bSaleBill.MachiningSort
-- order by Xs_V_bSaleBill.Productid
Update BB_DepotKCList set Billname=convert(char(10),SaleDate,120) where Billname=''
commit
GO