SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PurchaseOrder]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[PurchaseOrder](
[PurchaseOrderNumber] [int] NOT NULL,
[CompanyName] [nvarchar](100) NULL,
[PurchaseOrderDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PurchaseOrderDescription]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[PurchaseOrderDescription](
[PurchaseOrderNumber] [int] NOT NULL,
[PartNumber] [nvarchar](25) NULL,
[ItemDescription] [nvarchar](100) NULL,
[Quantity] [int] NULL,
[Manufacturer] [nvarchar](100) NULL,
[TargetPrice] [decimal](10, 2) NULL,
[DeliveryDate] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[example]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[example](
[Id] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SavePurchaseOrder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SavePurchaseOrder]
@PurchaseOrderNumber int,
@CompanyName nvarchar(100),
@PartNumber nvarchar(25),
@ItemDescription nvarchar(100),
@Quantity int,
@Manufacturer nvarchar(100),
@TargetPrice decimal(10,2),
@DeliveryDate datetime
AS
BEGIN
if not Exists(select PurchaseOrderNumber from PurchaseOrder where PurchaseOrderNumber=@PurchaseOrderNumber)
begin
insert into PurchaseOrder(PurchaseOrderNumber,CompanyName,PurchaseOrderDate)
values(@PurchaseOrderNumber, @CompanyName,null)
insert into PurchaseOrderDescription(PurchaseOrderNumber,PartNumber,ItemDescription,Quantity,Manufacturer,TargetPrice,DeliveryDate)
values(@PurchaseOrderNumber,@PartNumber,@ItemDescription,@Quantity,@Manufacturer,@TargetPrice,@DeliveryDate)
end
else
begin
insert into PurchaseOrderDescription(PurchaseOrderNumber,PartNumber,ItemDescription,Quantity,Manufacturer,TargetPrice,DeliveryDate)
values(@PurchaseOrderNumber,@PartNumber,@ItemDescription,@Quantity, @Manufacturer,@TargetPrice,@DeliveryDate)
end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeletePurchaseOrder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeletePurchaseOrder]
@PurchaseOrderNumber int,
@PartNumber nvarchar(25)
AS
BEGIN
Declare
@No_Of_Records int
delete from PurchaseOrderDescription where (PurchaseOrderNumber=@PurchaseOrderNumber and PartNumber=@PartNumber)
set @No_Of_Records=(select count(PurchaseOrderNumber) from PurchaseOrderDescription where PurchaseOrderNumber=@PurchaseOrderNumber)
if(@No_Of_Records =0)
delete from PurchaseOrder where PurchaseOrderNumber=@PurchaseOrderNumber
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetAllPartNumberByPurchaseOrder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetAllPartNumberByPurchaseOrder]
@PurchaseOrderNumber int
AS
BEGIN
select * from dbo.PurchaseOrderDescription where PurchaseOrderNumber=@PurchaseOrderNumber
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UpdatePurchaseOrder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdatePurchaseOrder]
@PurchaseOrderNumber int,
@PartNumber nvarchar(25),
@ItemDescription nvarchar(100),
@Quantity int,
@Manufacturer nvarchar(100),
@TargetPrice decimal(10,2),
@DeliveryDate datetime
AS
BEGIN
Update dbo.PurchaseOrderDescription
set ItemDescription=@ItemDescription,Quantity=@Quantity,Manufacturer=@Manufacturer,TargetPrice=@TargetPrice,DeliveryDate=@DeliveryDate
where (PurchaseOrderNumber=@PurchaseOrderNumber and PartNumber= @PartNumber)
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddUser]
@ID int,
@FirstName nvarchar(250),
@LastName nvarchar(250)
AS
BEGIN
insert into example(Id,FirstName,LastName) values(@ID,@FirstName,@LastName)
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetAllUsers]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetAllUsers]
AS
BEGIN
select * from example
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteUser]
@ID int
AS
BEGIN
delete example where Id=@ID
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UpdateUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateUser]
@ID int,
@firstname nvarchar(250),
@lastname nvarchar(250)
AS
BEGIN
update example
set FirstName=@firstname, LastName=@lastname
where Id=@ID
END
'
END