ProductManager.sql

最新推荐文章于 2021-05-12 13:46:39 发布

美好晨光

最新推荐文章于 2021-05-12 13:46:39 发布

阅读量481

点赞数

分类专栏： ProductManager 文章标签： insert delete date null email table

本文链接：https://blog.csdn.net/kimcheolhyun/article/details/4917197

版权

ProductManager 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

use master
go
if DB_ID('ProductSet') is not null --判断指定的数据库是否存在
   drop database ProductSet
go
create database ProductSet
go
use ProductSet
go
create table UserLogin --用户表
(
UserID varchar(10) constraint PK_User Primary Key,
UserName varchar(20) unique not null,
UserPassword varchar(30) not null,
UserAccess nchar(4) check(UserAccess in('前台管理','后台管理'))
)
go
create proc Validate_UserLogin
(
@UserName varchar(20),
@UserPassword varchar(30),
@UserAccess nchar(4) output
)
as
set nocount on
select @UserAccess=UserAccess from UserLogin where UserName=@UserName and UserPassword=@UserPassword
go
create proc Insert_UserLogin
(
@UserID varchar(10),
@UserName varchar(20),
@UserPassword varchar(30),
@UserAccess nchar(4)
)
as
set nocount on
if exists(select * from UserLogin where UserID=@UserID)
   return
insert into UserLogin values(@UserID, @UserName, @UserPassword, @UserAccess)
go
execute Insert_UserLogin 'U001','jin','hP/yBlmZniuDtFxoUexX3Q','前台管理' -- pwd = 'jin'
execute Insert_UserLogin 'U002','jzx','eLFnae+a8wPfJxAJ7jw/oA','后台管理' -- pwd = 'jzx'
select * from UserLogin
go
create proc Update_UserLogin
(
@UserID varchar(10),
@UserName varchar(20),
@UserPassword varchar(30),
@UserAccess nchar(4)
)
as
set nocount on
if exists(select * from UserLogin where userID=@userID)
begin
begin tran
update UserLogin set UserName=@UserName, UserPassword=@UserPassword, UserAccess=@UserAccess where UserID=@UserID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into UserLogin values(@UserID, @UserName, @UserPassword, @UserAccess)
go
create proc Delete_UserLogin( @UserID varchar(10) )
as
set nocount on
if exists(select * from UserLogin where UserID=@UserID)
delete from UserLogin where UserID=@UserID
go
create table Worker --员工表
(
WorkerID varchar(10) constraint PK_Worker Primary Key,  --员工编码
WorkerName varchar(20),      --员工姓名
DepartmentName varchar(20),     --部门名称
Sex nchar(1) check(Sex in('男','女')),    --性别
Birthday smalldatetime,      --出生日期
Age tinyint check(len(Age)=2),     --tinyint 0 到 255 存储 1 字节
Jiguan varchar(20),
Minzu varchar(20),
Xueli varchar(20),
Single nchar(2) check(Single in('未婚','已婚','离婚')),  --婚姻状况
CardID varchar(20),       --身份证
Phone varchar(15),      --宅电
TEL varchar(15),      --手机
[Address] nvarchar(100),     --地址
DataImage Image       --头像
)
go
create proc Insert_Worker
(
@WorkerID varchar(10),
@WorkerName varchar(20),
@DepartmentName varchar(20),
@Sex nchar(1),
@Birthday smalldatetime,
@Age tinyint,
@Jiguan varchar(20),
@Minzu varchar(20),
@Xueli varchar(20),
@Single nchar(2),
@CardID varchar(20),
@Phone varchar(15),
@TEL varchar(15),
@Address nvarchar(100),
@DataImage Image
)
as
set nocount on
if exists(select * from Worker where WorkerID=@WorkerID)
   return
insert into Worker values(@WorkerID,@WorkerName,@DepartmentName,@Sex,@Birthday,@Age,@Jiguan,@Minzu,@Xueli,@Single,@CardID,@Phone,@TEL,@Address,@DataImage)
go
create proc Update_Worker
(
@WorkerID varchar(10),
@WorkerName varchar(20),
@DepartmentName varchar(20),
@Sex nchar(1),
@Birthday smalldatetime,
@Age tinyint,
@Jiguan varchar(20),
@Minzu varchar(20),
@Xueli varchar(20),
@Single nchar(2),
@CardID varchar(20),
@Phone varchar(15),
@TEL varchar(15),
@Address nvarchar(100),
@DataImage Image
)
as
set nocount on
if exists(select * from Worker where WorkerID=@WorkerID)
begin
begin tran
update Worker set WorkerName=@WorkerName,DepartmentName=@DepartmentName,Sex=@Sex,Birthday=@Birthday,Age=@Age,Jiguan=@Jiguan,Minzu=@Minzu,
Xueli=@Xueli,Single=@Single,CardID=@CardID,Phone=@Phone,TEL=@TEL,[Address]=@Address,DataImage=@DataImage where WorkerID= @WorkerID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into Worker values(@WorkerID,@WorkerName,@DepartmentName,@Sex,@Birthday,@Age,@Jiguan,@Minzu,@Xueli,@Single,@CardID,@Phone,@TEL,@Address,@DataImage)
go
create proc Delete_Worker(@WorkerID varchar(10))
as
set nocount on
if exists(select * from Worker where WorkerID=@WorkerID)
delete from Worker where WorkerID= @WorkerID
go
create table Client --客户表
(
ClientID varchar(10) constraint PK_Client Primary Key,
ClientName varchar(20) unique not null,
Linkman varchar(20) not null,
LinkPhone varchar(15) not null,
Fax varchar(15),
Email varchar(30),
[Address] nvarchar(100) not null,
Memo nvarchar(50)
)
go
create proc Insert_Client
(
@ClientID varchar(10),
@ClientName varchar(20),
@Linkman varchar(20),
@LinkPhone varchar(15),
@Fax varchar(15),
@Email varchar(30),
@Address nvarchar(100),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Client where ClientID=@ClientID)
   return
insert into Client values(@ClientID, @ClientName, @Linkman, @LinkPhone, @Fax, @Email, @Address, @Memo)
go
execute Insert_Client 'C001','三星公司','姓名1','0431-64549245','0431-64549245','Client@hotmail.com','地址1','备注'
execute Insert_Client 'C002','惠普公司','姓名2','0431-64549245','0431-64549245','Client@hotmail.com','地址2','备注'
execute Insert_Client 'C003','SONY公司','姓名3','0431-64549245','0431-64549245','Client@hotmail.com','地址3','备注'
execute Insert_Client 'C004','联想公司','姓名4','0431-64549245','0431-64549245','Client@hotmail.com','地址4','备注'
execute Insert_Client 'C005','明基公司','姓名5','0431-64549245','0431-64549245','Client@hotmail.com','地址5','备注'
go
create proc Update_Client
(
@ClientID varchar(10),
@ClientName varchar(20),
@Linkman varchar(20),
@LinkPhone varchar(15),
@Fax varchar(15),
@Email varchar(30),
@Address nvarchar(100),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Client where ClientID=@ClientID)
begin
begin tran
update Client set ClientName=@ClientName, Linkman=@Linkman, LinkPhone=@LinkPhone,
Fax=@Fax, Email=@Email, [Address]=@Address, Memo=@Memo where ClientID=@ClientID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into Client values(@ClientID, @ClientName, @Linkman, @LinkPhone, @Fax, @Email, @Address, @Memo)
go
create proc Delete_Client( @ClientID varchar(10) )
as
set nocount on
if exists(select * from Client where ClientID=@ClientID)
delete from Client where ClientID=@ClientID
go
create table Provider -- 供应商表
(
ProviderID varchar(10) constraint PK_Provider Primary Key,
ProviderName varchar(20) unique not null,
Linkman varchar(20) not null,
LinkPhone varchar(15) not null,
Fax varchar(15),
Email varchar(30) not null,
[Address] nvarchar(100) not null,
Bank varchar(30),
BankAccount varchar(30),
Memo nvarchar(50)
)
go
create proc Insert_Provider
(
@ProviderID varchar(10),
@ProviderName varchar(20),
@Linkman varchar(20),
@LinkPhone varchar(15),
@Fax varchar(15),
@Email varchar(30),
@Address nvarchar(100),
@Bank varchar(30),
@BankAccount varchar(30),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Provider where ProviderID=@ProviderID)
   return
insert into Provider values(@ProviderID, @ProviderName, @Linkman, @LinkPhone, @Fax, @Email, @Address, @Bank, @BankAccount, @Memo)
go
execute Insert_Provider 'PV001','三星公司','man1','0431-64549245','0431-64549245','Provider@hotmail.com','地址1','工商银行','','备注1'
execute Insert_Provider 'PV002','联想公司','man2','0431-64549245','0431-64549245','Provider@hotmail.com','地址2','建设银行','','备注2'
execute Insert_Provider 'PV003','SONY公司','man3','0431-64549245','0431-64549245','Provider@hotmail.com','地址3','交通银行','','备注3'
execute Insert_Provider 'PV004','惠普公司','man4','0431-64549245','0431-64549245','Provider@hotmail.com','地址4','中国人民银行','','备注4'
go
create proc Update_Provider
(
@ProviderID varchar(10),
@ProviderName varchar(20),
@Linkman varchar(20),
@LinkPhone varchar(15),
@Fax varchar(15),
@Email varchar(30),
@Address nvarchar(100),
@Bank varchar(30),
@BankAccount varchar(30),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Provider where ProviderID=@ProviderID)
begin
begin tran
UPDATE Provider SET ProviderName=@ProviderName, Linkman=@Linkman, LinkPhone=@LinkPhone, Fax=@Fax, Email=@Email,
[Address]=@Address, Bank=@Bank, BankAccount=@BankAccount, Memo=@Memo WHERE ProviderID=@ProviderID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into Provider values(@ProviderID, @ProviderName, @Linkman, @LinkPhone, @Fax, @Email, @Address, @Bank, @BankAccount, @Memo)
go
create proc Delete_Provider( @ProviderID varchar(10) )
as
set nocount on
if exists(select * from Provider where ProviderID=@ProviderID)
delete from Provider where ProviderID = @ProviderID
go
create table ProductType
(
TypeID varchar(10) constraint PK_ProductType Primary Key,
TypeName varchar(20) unique not null
)
go
create proc InsertProductType
(
@TypeID varchar(10),
@TypeName varchar(20)
)
as
set nocount on
if exists(select * from ProductType where TypeID=@TypeID)
   return
insert into ProductType values(@TypeID, @TypeName)
go
exec InsertProductType 'PT001','电子产品'
exec InsertProductType 'PT002','家具'
exec InsertProductType 'PT003','食品'
exec InsertProductType 'PT004','服装'
go
create proc UpdateProductType
(
@TypeID varchar(10),
@TypeName varchar(20)
)
as
set nocount on
if exists(select * from ProductType where TypeID=@TypeID)
begin
begin tran
update ProductType set TypeName=@TypeName where TypeID=@TypeID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into ProductType values(@TypeID, @TypeName)
go
create proc DeleteProductType
(
@TypeID varchar(10)
)
as
set nocount on
if exists(select * from ProductType where TypeID=@TypeID)
delete from ProductType where TypeID=@TypeID
go
create table Product --库存表
(
ProductID varchar(10) constraint PK_Product Primary Key,
ProductName varchar(20) unique not null,
ProductType varchar(20) constraint FK_Product Foreign Key(ProductType) references ProductType(TypeName) on update cascade,
Unit varchar(4) not null,
Number int default 0,
BuyPrice decimal(10,2) default 0.00,
SellPrice decimal(10,2) default 0.00,
Memo nvarchar(50)
)
go
create proc Insert_Product
(
@ProductID varchar(10),
@ProductName varchar(20),
@ProductType varchar(20),
@Unit varchar(4),
@Number int =0,
@BuyPrice decimal(10,2) =0.00,
@SellPrice decimal(10,2) =0.00,
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Product where ProductID=@ProductID)
   return
begin
begin tran
insert into Product values(@ProductID, @ProductName, @ProductType, @Unit, @Number, @BuyPrice, @SellPrice, @Memo)
if (@@error = 0)
    commit tran
else
    rollback tran
end
go
exec Insert_Product 'P001','电脑','电子产品','单位',100,'100.00','100.00','备注'
exec Insert_Product 'P002','打印机','电子产品','单位',200,'100.00','100.00','备注'
exec Insert_Product 'P003','安全门','家具','单位',300,'100.00','100.00','备注'
exec Insert_Product 'P004','照相机','电子产品','单位',400,'100.00','100.00','备注'
exec Insert_Product 'P005','投影机','电子产品','单位',1000,'100.00','100.00','备注'
go
create proc Update_Product
(
@ProductID varchar(10),
@ProductName varchar(20),
@ProductType varchar(20),
@Unit varchar(4),
@Number int =0,
@BuyPrice decimal(10,2) =0.00,
@SellPrice decimal(10,2) =0.00,
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from Product where ProductID=@ProductID)
begin
begin tran
update Product set ProductName=@ProductName, ProductType=@ProductType, Unit=@Unit, Number=@Number,
BuyPrice=@BuyPrice, SellPrice=@SellPrice, Memo=@Memo where ProductID=@ProductID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into Product values(@ProductID, @ProductName, @ProductType, @Unit, @Number, @BuyPrice, @SellPrice, @Memo)
go
create proc Delete_Product( @ProductID varchar(10) )
as
set nocount on
if exists(select * from Product where ProductID=@ProductID)
begin
begin tran
delete from Product where ProductID=@ProductID
if (@@error = 0)
    commit tran
else
    rollback tran
end
go
create table ProductBuy --商品采购表
(
BuyID varchar(10) constraint PK_ProductBuy Primary Key,
ProductID varchar(10) constraint FK_ProductBuy Foreign Key(ProductID) references Product(ProductID),
ProviderID varchar(10) constraint FK_Provider_ProductSell Foreign Key(ProviderID) references Provider(ProviderID),
Unit varchar(4) not null,
Price decimal(10,2) default 0.00,
Number int default 0,
StartDate smalldatetime check(StartDate<=getdate()) default getdate(),
OverDate smalldatetime,
WorkerID varchar(10) not null constraint FK_Worker_ProductBuy Foreign Key(WorkerID) references Worker(WorkerID),
Memo nvarchar(50)
)
go
create proc Insert_ProductBuy
(
@BuyID varchar(10),
@ProductID varchar(10),
@ProviderID varchar(10),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@StartDate smalldatetime,
@OverDate smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductBuy where BuyID=@BuyID)
   return
insert into ProductBuy values(@BuyID, @ProductID, @ProviderID, @Unit, @Price, @Number, @StartDate, @OverDate, @WorkerID, @Memo)
go
create proc Update_ProductBuy
(
@BuyID varchar(10),
@ProductID varchar(10),
@ProviderID varchar(10),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@StartDate smalldatetime,
@OverDate smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductBuy where BuyID=@BuyID)
begin
begin tran
update ProductBuy set ProductID=@ProductID, ProviderID=@ProviderID, Unit=@Unit, Price=@Price, Number=@Number,
StartDate=@StartDate, OverDate=@OverDate, WorkerID=@WorkerID, Memo= @Memo where BuyID=@BuyID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into ProductBuy values(@BuyID, @ProductID, @ProviderID, @Unit, @Price, @Number, @StartDate, @OverDate, @WorkerID, @Memo)
go
create proc Delete_ProductBuy(@BuyID varchar(10))
as
set nocount on
if exists(select * from ProductBuy where BuyID=@BuyID)
delete from ProductBuy where BuyID=@BuyID
go
create table ProductIn --商品入库表
(
InID varchar(10) constraint PK_ProductIn Primary Key,
BuyID varchar(10) constraint FK_ProductBuy_BuyID Foreign Key(BuyID) references ProductBuy(BuyID),
ProductID varchar(10) constraint FK_ProductIn Foreign Key(ProductID) references Product(ProductID),
[Type] varchar(20) not null,
Unit varchar(4) not null,
Price decimal(10,2) default 0.00,
Number int default 0,
[Date] smalldatetime check([Date]<=getdate()) default getdate(),
WorkerID varchar(10) constraint FK_ProductIn_WorkerID Foreign Key(WorkerID) references Worker(WorkerID),
Memo nvarchar(50)
)
go
create proc Insert_ProductIn
(
@InID varchar(10),
@BuyID varchar(10),
@ProductID varchar(10),
@Type varchar(20),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@Date smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductIn where InID=@InID)
return
begin
begin tran
declare @InError int, @ProductError int
insert into ProductIn values(@InID,@BuyID,@ProductID,@Type,@Unit,@Price,@Number,@Date,@WorkerID,@Memo)
select @InError=@@Error
update Product set Number=Number + @Number where ProductID=@ProductID
select @ProductError=@@Error
if (@InError = 0 and @ProductError = 0)
    commit tran
else
    rollback tran
end
go
create proc Update_ProductIn
(
@InID varchar(10),
@BuyID varchar(10),
@ProductID varchar(10),
@Type varchar(20),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@Date smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductIn where InID=@InID)
begin
begin tran
declare @InError int, @ProductError int, @ProductNumber int
update ProductIn set BuyID=@BuyID, ProductID=@ProductID, [Type]=@Type, Unit=@Unit, Price=@Price, Number=@Number,
[Date]=@Date, WorkerID=@WorkerID, Memo= @Memo, @ProductNumber=@Number - Number where InID=@InID
select @InError=@@Error
update Product set Number=Number + @ProductNumber where ProductID=@ProductID
select @ProductError=@@Error
if (@InError = 0 and @ProductError = 0)
    commit tran
else
    rollback tran
end
else
execute Insert_ProductIn @InID,@BuyID,@ProductID,@Type,@Unit,@Price,@Number,@Date,@WorkerID,@Memo
go
create table ProductSell --商品订单表
(
SellID varchar(10) constraint PK_ProductSell Primary Key,
ProductID varchar(10) constraint FK_ProductSell Foreign Key(ProductID) references Product(ProductID),
ClientID varchar(10) constraint FK_Client_ProductSell Foreign Key(ClientID) references Client(ClientID),
Unit varchar(4) not null,
Price decimal(10,2) default 0.00,
Number int default 0,
StartDate smalldatetime check(StartDate<=getdate()) default getdate(),
OverDate smalldatetime,
WorkerID varchar(10) constraint FK_Worker_ProductSell Foreign Key(WorkerID) references Worker(WorkerID),
Memo nvarchar(50)
)
go
create proc Insert_ProductSell
(
@SellID varchar(10),
@ProductID varchar(10),
@ClientID varchar(10),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@StartDate smalldatetime,
@OverDate smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductSell where SellID=@SellID)
   return
insert into ProductSell values(@SellID, @ProductID, @ClientID, @Unit, @Price, @Number, @StartDate, @OverDate, @WorkerID, @Memo)
go
create proc Update_ProductSell
(
@SellID varchar(10),
@ProductID varchar(10),
@ClientID varchar(10),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@StartDate smalldatetime,
@OverDate smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductSell where SellID=@SellID)
begin
begin tran
update ProductSell set ProductID=@ProductID, ClientID=@ClientID, Unit=@Unit, Price=@Price, Number=@Number,
StartDate=@StartDate, OverDate=@OverDate, WorkerID=@WorkerID, Memo=@Memo where SellID=@SellID
if (@@error = 0)
    commit tran
else
    rollback tran
end
else
insert into ProductSell values(@SellID, @ProductID, @ClientID, @Unit, @Price, @Number, @StartDate, @OverDate, @WorkerID, @Memo)
go
create proc Delete_ProductSell(@SellID varchar(10))
as
set nocount on
if exists(select * from ProductSell where SellID=@SellID)
delete from ProductSell where SellID=@SellID
go
create table ProductOut  --商品出库表
(
OutID varchar(10) constraint PK_ProductOut Primary Key,
SellID varchar(10) constraint FK_ProductSell_SellID Foreign Key(SellID) references ProductSell(SellID),
ProductID varchar(10) constraint FK_ProductOut Foreign Key(ProductID) references Product(ProductID),
[Type] varchar(20) not null,
Unit varchar(4) not null,
Price decimal(10,2) default 0.00,
Number int default 0,
[Date] smalldatetime check([Date]<=getdate()) default getdate(),
WorkerID varchar(10) constraint FK_ProductOut_WorkerID Foreign Key(WorkerID) references Worker(WorkerID),
Memo nvarchar(50)
)
go
create proc Insert_ProductOut
(
@OutID varchar(10),
@SellID varchar(10),
@ProductID varchar(10),
@Type varchar(20),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@Date smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductOut where OutID=@OutID)
return
begin
begin tran
declare @OutError int, @ProductError int
insert into ProductOut values(@OutID,@SellID,@ProductID,@Type,@Unit,@Price,@Number,@Date,@WorkerID,@Memo)
select @OutError=@@Error
update Product set Number=Number - @Number where ProductID=@ProductID
select @ProductError=@@Error
if (@OutError = 0 and @ProductError = 0)
    commit tran
else
    rollback tran
end
go
create proc Update_ProductOut
(
@OutID varchar(10),
@SellID varchar(10),
@ProductID varchar(10),
@Type varchar(20),
@Unit varchar(4),
@Price decimal(10,2) =0.00,
@Number int =0,
@Date smalldatetime,
@WorkerID varchar(10),
@Memo nvarchar(50)
)
as
set nocount on
if exists(select * from ProductOut where OutID=@OutID)
begin
begin tran
declare @OutError int, @ProductError int, @ProductNumber int
update ProductOut set SellID=@SellID, ProductID=@ProductID, [Type]=@Type, Unit=@Unit, Price=@Price, Number=@Number,
[Date]=@Date, WorkerID=@WorkerID, Memo= @Memo, @ProductNumber=@Number - Number where OutID=@OutID
select @OutError=@@Error
update Product set Number=Number - @ProductNumber where ProductID=@ProductID
select @ProductError=@@Error
if (@OutError = 0 and @ProductError = 0)
    commit tran
else
    rollback tran
end
else
execute Insert_ProductOut @OutID,@SellID,@ProductID,@Type,@Unit,@Price,@Number,@Date,@WorkerID,@Memo
go

create proc Select_TotalInfo
(
@ProductID varchar(10) =''
)
as
set nocount on
select ProductID, ProductType, Unit, isnull(sum(Number), 0) Number, (select isnull(sum(Number), 0) from ProductIn where ProductID=@ProductID) InNumber,
(select isnull(sum(Number), 0) from ProductOut where ProductID=@ProductID) OutNumber from Product
where ProductID=@ProductID
group by ProductID, ProductType, Unit
go
create proc Select_TotalDateInfo
(
@ProductID varchar(10) ='',
@BeginDate smalldatetime,
@EndDate smalldatetime
)
as
set nocount on
if exists(select * from ProductIn where ProductID=@ProductID and [Date] between @BeginDate and @EndDate)
or exists(select * from ProductOut where ProductID=@ProductID and [Date] between @BeginDate and @EndDate)
begin
select ProductID, ProductType, Unit, isnull(sum(Number), 0) Number, (select isnull(sum(Number), 0) from ProductIn where ProductID=@ProductID and [Date] between @BeginDate and @EndDate) InNumber,
(select isnull(sum(Number), 0) from ProductOut where ProductID=@ProductID and [Date] between @BeginDate and @EndDate) OutNumber from Product
where ProductID=@ProductID
group by ProductID, ProductType, Unit
end
go