create procedure p
@SPrice money, --货物单价
@StoreID varchar(100) --物品库存号
as
update 入库表
set SPrice=@SPrice,
TPrice=@SPrice*Num
where StoreID=@StoreIDselect @@rowcount
go
@SPrice money, --货物单价
@StoreID varchar(100) --物品库存号
as
update 入库表
set SPrice=@SPrice,
TPrice=@SPrice*Num
where StoreID=@StoreIDselect @@rowcount
go
(
@sprice money,
@StoreID varchar(20)
)
update 入库表
set sprice =@sprice,
Tprice =@sprice* 库存数量Num
where StoreID = @StoreID
to: zlp321002(想在北京找份工作!)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoreIn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StoreIn]
GOCREATE TABLE [dbo].[StoreIn] (
[INID] [int] IDENTITY (1, 1) NOT NULL ,
[StoreID] [int] NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NewType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Factory] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Brand] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[InNum] [float] NULL ,
[SPrice] [money] NULL ,
[TPrice] [money] NULL ,
[InType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[InData] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[InMan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Re] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[StuffType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PrjctNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PrjctName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Unit] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Storage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cancel] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoreIn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StoreIn]
GOCREATE TABLE [dbo].[StoreIn] (
[INID] [int] IDENTITY (1, 1) NOT NULL ,
[StoreID] [int] NULL ,--库存号
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--物料名称
[NewType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--型号
[Factory] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--厂商
[Brand] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--品牌
[InNum] [float] NULL ,--入库数量
[SPrice] [money] NULL ,--单价
[TPrice] [money] NULL ,--总价
[InType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--入库类型
[InData] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--入库时间
[InMan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--入库人
[Re] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,--备注
[StuffType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--物料类型
[PrjctNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--合同名称
[PrjctName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--项目名称
[Unit] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--单位
[Storage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--仓库
[Cancel] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL --是否取消
) ON [PRIMARY]
GO存储过程只处理入库表就行了。
-------------------------------
不过如果可以一次处理入库表、出库表那就更好了,下边是出库表的结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoreOut]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StoreOut]
GOCREATE TABLE [dbo].[StoreOut] (
[OutID] [int] IDENTITY (1, 1) NOT NULL ,
[StoreID] [int] NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[NewType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Factory] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Brand] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OutNum] [float] NULL ,
[SPrince] [money] NULL ,
[TPrice] [money] NULL ,
[OutType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OutData] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OutMan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Re] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[GetMan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[StuffType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PrjctNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PrjctName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Storage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
@TableName Nvarchar (50),--表名
@SPrice money, --货物单价
@StoreID int --物品库存号
as
if @TableName == 'StoreIn'--说这里==附近有错
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
endif @TableName == 'StoreOut'--说这里==附近有错
begin
update StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutMan
where StoreID=@StoreID And Cancel is null
endGO
该怎么写呢?
@TableName Nvarchar (50), --表名
@SPrice money, --货物单价
@StoreID varchar(100) --物品库存号
as
declare @sql nvarchar(1000)
set @sql=N' update '+@TableName+
N' set SPrice=@SPrice,'+
N' TPrice=@SPrice*'+(case when @TableName=N'StoreIn'
then N'InNUm'
else N'OutNum'
end)+
N' where StoreID=@StoreID'
exec sp_executesql @sql,
N'@SPrice money,@StoreID varchar(100)',
@SPrice,@StoreID--返回受影响的行数
select @@rowcount
go
@TableName Nvarchar (50), --表名
@SPrice money, --货物单价
@StoreID varchar(100) --物品库存号
as
declare @sql nvarchar(1000)
set @sql=N' update '+@TableName+
N' set SPrice=@SPrice,'+
N' TPrice=@SPrice*'+(case when @TableName=N'StoreIn'
then N'InNUm'
else N'OutNum'
end)+
N' where StoreID=@StoreID '+
N' And Cancel is null'
exec sp_executesql @sql,
N'@SPrice money,@StoreID varchar(100)',
@SPrice,@StoreID--返回受影响的行数
select @@rowcount
go
@TableName Nvarchar (50),--表名
@SPrice money, --货物单价
@StoreID int --物品库存号
as
if @TableName = 'StoreIn'
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
endif @TableName = 'StoreOut'
begin
update StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutMan
where StoreID=@StoreID And Cancel is null
end--返回受影响的行数
select @@rowcountGO
CREATE procedure ResetPrice
@TableName Nvarchar (50),--表名
@StoreID int , --物品库存号
@SPrice money , --货物单价
@Result int Output --受影响的记录条数as
if @TableName = 'StoreIn'
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
endif @TableName = 'StoreOut'
begin
update StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
endif @TableName = 'TwoTable'
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @resultupdate StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
end
GO
@TableName Nvarchar (50),--表名
@StoreID int , --物品库存号
@SPrice money , --货物单价
@Result int Output --受影响的记录条数as
--初始值
select @Result=0if @TableName = 'StoreIn'
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
endif @TableName = 'StoreOut'
begin
update StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
endif @TableName = 'TwoTable'
begin
update StoreIn
set SPrice=@SPrice,
TPrice=@SPrice*InNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @resultupdate StoreOut
set SPrice=@SPrice,
TPrice=@SPrice*OutNum
where StoreID=@StoreID And Cancel is null
set @result = @@rowcount + @result
end
GO