Today, I will show you the below mentioned important steps
- CREATE TABLES.
- CREATE STORED PROCEDURE USING INNER JOIN BETWEEN TWO TABLES.
- FIND OUT STORED PROCEDURE SYNTAX AS TEXT USING SQL QUERY.
- EXECUTE STORED PROCEDURE TO GET RESULTS.
- LIST OF TABLES USED IN A STORED PROCEDURE.
- THEN CREATE DATABASE DIAGRAM.
STEP 1
CREATE ONE TABLE NAMED Supplier_Type_Master.
SQL syntax
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Supplier_Type_Master](
[supp_type_id] [char](2) NOT NULL,
[supp_type] [varchar](30) NOT NULL,
CONSTRAINT [PK_Supplier_Type_Master] PRIMARY KEY CLUSTERED
(
[supp_type_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Design Output
INSERT data output
STEP 2 - CREATE SECOND TABLE NAMED Supplier_Master
Before creating this table, I have to create a constraint.
Named DF_Supplier_Master_user_delete_flag.
Note
The constraints are used on a column or a table such that wrong data can't be inserted into the tables. The constraints helps for data integrity and accuracy in the table.
SQL syntax
USE [MyDB]
GO
ALTER TABLE [dbo].[Supplier_Master] ADD CONSTRAINT [DF_Supplier_Master_user_delete_flag] DEFAULT ('N') FOR [user_delete_flag]
GO
Now, create one foreign key relationship between Supplier_master and supplier_type_master.
Note
The foreign key is a table that uniquely identifies a row of other table. The foreign key is defined in a second table but it refers to the primary key in the first table.
SQL syntax
USE [MyDB]
GO
ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])
REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])
GO
ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]
GO
Finally, we create a table named Supplier_Master.
SQL syntax
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Supplier_Master](
[party_id] [varchar](40) NOT NULL,
[supplier_name] [varchar](50) NOT NULL,
[supplier_creation_date] [datetime] NOT NULL,
[supp_type_id] [char](2) NOT NULL,
[insurance_details] [varchar](4000) NOT NULL,
[vendor_option_in] [varchar](15) NOT NULL,
[memo] [varchar](4000) NULL,
[file_name] [varchar](100) NULL,
[file_path] [varchar](250) NULL,
[time_stamp_inserted] [datetime] NOT NULL,
[time_stamp_modified] [datetime] NULL,
[time_stamp_deleted] [datetime] NULL,
[user_delete_flag] [char](1) NOT NULL CONSTRAINT [DF_Supplier_Master_user_delete_flag]DEFAULT ('N'),
[user_login_name] [varchar](100) NOT NULL,
CONSTRAINT [PK_Supplier_Master] PRIMARY KEY CLUSTERED
(
[party_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master] FOREIGN KEY([supp_type_id])
REFERENCES [dbo].[Supplier_Type_Master] ([supp_type_id])
GO
ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [FK_Supplier_Master_Supplier_Type_Master]
GO
Design output
INSERT data output
Join the conversation! Your thoughts help the community grow.