SQL Server advanced query and T-SQL programming to realize warehouse inventory management system

1. Language and environment

1. Implementation language: T-SQL
2. Environment requirements: SQL Server 2012 and above.

2. Functional requirements

A company needs to develop a warehouse inventory management system, existing database concept model and data dictionary

Conceptual model:

Physical model:

Data dictionary:

Property name	explain	remarks
ProductID	Item ID	Primary key, auto increment
ProductName	description of goods	Constraints: non empty
Quantity	Inventory quantity	Constraint: greater than 0
RecodeID	Record No	Primary key, auto increment
Count	Quantity in and out of warehouse
RecodeType	Type of in and out warehouse	0: receipt 1: issue
RecodeDate	In and out date

3. Using DDL to create database StorageDB

--1. use DDL Create database StorageDB
create database StorageDB
--Open library
use StorageDB

4. Use DDL to create the structure and constraints of the products info and the recode respectively

--2. use DDL Create inventory item table separately(ProductsInfo)And warehouse in and out records(Recode)Structure and constraints
create table ProductsInfo(
	ProductID int identity(1,1) primary key not null,
	ProductName varchar(50) not null,
	Quantity int not null
)
create table Recode(
	RecodeID int identity(1,1) primary key not null,
	ProductID int references ProductsInfo(ProductID),--Pay attention to foreign key relationship
	Count int not null,
	RecodeType smallint not null,
	RecodeDate date not null
)

5. Use DML to insert the following data into the data table

--3. use DML Insert the following data into the data table
insert into ProductsInfo values('Dell computer',100)
insert into ProductsInfo values('Ray ban keyboard',150)
insert into ProductsInfo values('iPhone mobile phone',200)
select * from ProductsInfo

insert into Recode values(1,300,0,'2019-08-22')
insert into Recode values(1,150,1,'2019-08-25')
insert into Recode values(1,50,1,'2019-09-22')
insert into Recode values(2,100,0,'2019-08-22')
insert into Recode values(2,50,0,'2019-08-25')
insert into Recode values(3,300,0,'2019-08-22')
insert into Recode values(3,100,1,'2019-08-25')
select * from Recode

You can also use batch insert. The two effects are the same.

insert into ProductsInfo
select 'Dell computer' ,100 union
select 'Ray ban keyboard' ,150 union
select 'iPhone mobile phone',200
insert into Recode 
select 1,300,0,'2019-8-22' union
select 1,150,1,'2019-8-25' union
select 1,50,1,'2019-8-22' union
select 2,100,0,'2019-8-22' union
select 2,50,0,'2019-8-25' union
select 3,300,0,'2019-8-22'union
select 3,100,1,'2019-8-25' 
go

6. Create a view to query the in and out details. The query fields are item ID, item name, in and out quantity, in and out type and in and out date

Stock in / stock out type in the view needs to display stock in / stock out

-- 4. Create a view to query the in and out details. The query field is goods ID,Product name, quantity, type and date
--Judge whether the view exists or not, delete if it exists
if exists(select * from sysobjects where name='v_Details')
	drop view v_Details
go
create view v_Details as
select p.ProductID 'Goods ID',ProductName 'description of goods',Count 'Quantity in and out of warehouse'
,(case r.RecodeType when 0 then 'Warehousing' when 1 then 'Outbound' else 'nill' end )'Type of in and out warehouse',RecodeDate 'In and out date' 
from ProductsInfo p join Recode r on p.ProductID=r.ProductID
go

Here, case when is used to determine whether to stock in or stock out

5. Write stored procedures for in and out records, and use transactions.

The business process of the warehousing in and out records is: modify the stock quantity - > generate the warehousing in and out records. If the stock quantity is less than 0, the warehousing in and out process cannot be completed

--5. Write stored procedures for in and out records, write 5 statements and test, requiring the use of transactions.
if object_id('proc_chuku') is not null
	drop proc proc_chuku 
go
create proc proc_chuku(@ProductName varchar(20),@churu varchar(20),@Quantity int)
as
   if exists(select * from ProductsInfo where ProductName=@ProductName)
		begin
			declare @ProductID int;--Goods id
			declare @Quantity2 int;--stock
			declare @sumError int=0;--Define statistical error information
			select @ProductID=ProductID,@Quantity2=Quantity from ProductsInfo where ProductName=@ProductName
				begin tran
					if @Quantity2-@Quantity<0
						begin
							print @churu+'fail,Insufficient inventory'
						end
						else
						begin
							if @churu='Outbound'
								begin
									update ProductsInfo set Quantity=Quantity-@Quantity where ProductID=@ProductID;
									set @sumError+=@@ERROR;--Statistics error information
									insert into Recode values(@ProductID,@Quantity,1,GETDATE())
									set @sumError+=@@ERROR;--Statistics error information
								end
								else
								begin
									update ProductsInfo set Quantity=Quantity+@Quantity where ProductID=@ProductID;
									set @sumError+=@@ERROR;--Statistics error information
									insert into Recode values(@ProductID,@Quantity,0,GETDATE())
									set @sumError+=@@ERROR;--Statistics error information
								end
							end
					if @sumError=0
				begin
					commit tran
					print @churu+'success'
				end
				else
				begin
					print @churu+'fail'
					rollback tran--Rollback transaction
				end
		end
	else
	print'The goods do not exist'
go

--Calling stored procedure with parameters
declare @ProductName varchar(20)='iPhone mobile phone'
declare @churu varchar(20)='Outbound'
declare @Quantity int=99
exec proc_chuku @ProductName,@churu,@Quantity;

That's the end. Let's practice! Welcome to talk with Monkey.

Thank you for pointing out Monkey
If you think it's useful, leave a compliment! Crabs and crabs