CREATE PROCEDURE [dbo].[sp_Supplier_SelectByID]
@SupplierID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[tb_Supplier] WITH (NOLOCK)
WHERE SupplierID = CASE WHEN @SupplierID = -1 THEN SupplierID ELSE @SupplierID END;
END
GO
CREATE PROCEDURE [dbo].[sp_Supplier_Insert]
@SupplierID INT OUTPUT,
@SupplierName NVARCHAR(250),
@InBusinessSince DATETIME2,
@TaxNumber NVARCHAR(50),
@LicenseNumber NVARCHAR(50),
@IsActive BIT,
@PreQualificationNumber NVARCHAR(50),
@PreQualificationDate DATETIME,
@PreQualificationExpiryDate DATETIME,
@UserID INT
AS
BEGIN
SET NOCOUNT ON;
IF ( EXISTS( SELECT [SupplierID]
FROM [dbo].[tb_Supplier]
WHERE [SupplierName] = @SupplierName
)
)
BEGIN
RAISERROR('Supplier already exists', 16, 1);
END
ELSE
BEGIN
INSERT INTO
[dbo].[tb_Supplier]
( SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn)
VALUES
( @SupplierName, @InBusinessSince, @TaxNumber, @LicenseNumber,
@IsActive,
@PreQualificationNumber, @PreQualificationDate, @PreQualificationExpiryDate,
@UserID, GETDATE());
SET @SupplierID = SCOPE_IDENTITY();
INSERT INTO
[dbo].[tb_SupplierLog]
( Operation, SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
SELECT 'INSERT', @SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
FROM [dbo].[tb_Supplier] WITH (NOLOCK)
WHERE SupplierID = @SupplierID;
END
END
GO
CREATE PROCEDURE [dbo].[sp_Supplier_Update]
@SupplierID INT OUTPUT,
@SupplierName NVARCHAR(250),
@InBusinessSince DATETIME2,
@TaxNumber NVARCHAR(50),
@LicenseNumber NVARCHAR(50),
@IsActive BIT,
@PreQualificationNumber NVARCHAR(50),
@PreQualificationDate DATETIME,
@PreQualificationExpiryDate DATETIME,
@UserID INT
AS
BEGIN
SET NOCOUNT ON;
IF ( EXISTS( SELECT [SupplierID]
FROM [dbo].[tb_Supplier]
WHERE [SupplierID] <> @SupplierID
AND [SupplierName] = @SupplierName
)
)
BEGIN
RAISERROR('Supplier already exists', 16, 1);
END
ELSE
BEGIN
UPDATE [dbo].[tb_Supplier]
SET SupplierName = @SupplierName,
InBusinessSince = @InBusinessSince,
TaxNumber = @TaxNumber,
LicenseNumber = @LicenseNumber,
IsActive = @IsActive,
PreQualificationNumber = @PreQualificationNumber,
PreQualificationDate = @PreQualificationDate,
PreQualificationExpiryDate = @PreQualificationExpiryDate,
UpdatedBy = @UserID,
UpdatedOn = GETDATE()
WHERE SupplierID = @SupplierID;
INSERT INTO
[dbo].[tb_SupplierLog]
( Operation, SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
SELECT 'UPDATE', @SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
FROM [dbo].[tb_Supplier] WITH (NOLOCK)
WHERE SupplierID = @SupplierID;
END
END
GO
CREATE PROCEDURE [dbo].[sp_Supplier_Delete]
@SupplierID INT,
@UserID INT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO
[dbo].[tb_SupplierLog]
( Operation, SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
SELECT 'DELETE', @SupplierID,
SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
IsActive,
PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
CreatedBy, CreatedOn, @UserID, GETDATE()
FROM [dbo].[tb_Supplier] WITH (NOLOCK)
WHERE SupplierID = @SupplierID;
DELETE FROM [dbo].[tb_Supplier]
WHERE SupplierID = @SupplierID;
END
GO
|