无详细内容 无 --Start Customer_SetCREATE PROCEDURE [dbo].[Customer_Set]@Name as varchar(50), @Address as varchar(250), @Mobile as varchar(50)ASBEGINSET NOCOUNT ON--ValidationIF @Name IS NULLBEGINRAISERROR ('Name cannot be empty.',16,1)ENDIF
--Start Customer_Set
CREATE PROCEDURE [dbo].[Customer_Set]
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50)
AS
BEGIN
SET NOCOUNT ON
--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Name cannot be empty.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Name cannot be less than 3 characters.',16,1)
END
--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (0)
END CATCH
RETURN (1)
END
--End Customer_Set
--Start Supplier_Set
CREATE PROCEDURE [dbo].[Supplier_Set]
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50)
AS
BEGIN
SET NOCOUNT ON
--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Please enter suppiler name.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
END
--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Supplier]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (-1)
END CATCH
RETURN (1)
END
--End Supplier_Set
--Start GetValidationConstraint
CRAETE PROCEDURE [dbo].[GetValidationConstraint]
--Output values
@EmptyCheck int OUTPUT,
@LenCheck int =NULL OUTPUT,
@NoDataExist int =NULL OUTPUT,
@True bit =NULL OUTPUT,
@False bit =NULL OUTPUT
AS
BEGIN
SELECT @EmptyCheck=1
SELECT @LenCheck =2
SELECT @NoDataExist =3
SELECT @True=1
SELECT @False=0
END
--End GetValidationConstraint
--Start ReturnMessage
CREATE PROCEDURE [dbo].[ReturnMessage]
--Success, Fail is the order of output parameter
@Success int OUTPUT,
@Fail int OUTPUT
AS
SET NOCOUNT ON
BEGIN
SELECT @Fail=0
SELECT @Success=1
END
--End ReturnMessage
--Start MessageHelper
CREATE PROCEDURE [dbo].[MessageHelper]
--Input values
@Field varchar(200) =NULL,
@MinLenght int =NULL,
@ValidationConstraint int,
--Output values
@ValidationMessage varchar(200) OUTPUT
AS
BEGIN
--Variables
DECLARE @EMPTY_MESSAGE varchar(50),
@MINIMUM_LENGHT_MESSAGE varchar(50),
@NO_DATA_EXIST_MESSAGE varchar(50)
DECLARE @EMPTY int,
@LEN int,
@NO_DATA_EXIST int
DECLARE @SUCCESSED int,
@FAILED int
--Message Constraint
SET @EMPTY_MESSAGE = 'cannot be empty.'
SET @MINIMUM_LENGHT_MESSAGE ='cannot be less than'
SET @NO_DATA_EXIST_MESSAGE = 'No record found.'
--Get global values
EXEC ReturnMessage @SUCCESSED output, @FAILED output
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT, @NO_DATA_EXIST OUTPUT
--Set message
IF @ValidationConstraint = @EMPTY
BEGIN
IF LEN(@Field)<=0
BEGIN
RAISERROR('Field name cannot be empty. StoreProcedure/MessageHelper',16,1)
RETURN @FAILED
END
SELECT @ValidationMessage = @Field + ' ' + @EMPTY_MESSAGE
END
IF @ValidationConstraint = @LEN
BEGIN
IF @MinLenght IS NULL OR @MinLenght <=0
BEGIN
RAISERROR('Minimum length cannot be empty. StoreProcedure/MessageHelper',16,1)
RETURN @FAILED
END
ELSE
BEGIN
SELECT @ValidationMessage = @Field + ' ' + @MINIMUM_LENGHT_MESSAGE + ' ' + CONVERT(varchar, @MinLenght)
END
END
IF @ValidationConstraint = @NO_DATA_EXIST
BEGIN
SELECT @ValidationMessage = @NO_DATA_EXIST_MESSAGE
END
END
--End MessageHelper
--Start Customer_Set
CREATE PROCEDURE [dbo].[Customer_Set]
--Input values
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50),
--Output values
@LASTID bigint OUTPUT,
@MESSAGE varchar(200) =NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN
--Constraint Variables For Readable Return Value
DECLARE @SUCCESSED int,
@FAILED int
--Constraint Variables For Readable Validation Operation
DECLARE @EMPTY int,
@LEN int
BEGIN TRY
--Get constraint value for successed and failed
EXEC ReturnMessage @SUCCESSED output, @FAILED output
--Get constraint value for validation. @EMPTY is for empty check and @LEN is for length check common messaging system.
EXEC GetValidationConstraint @EMPTY output, @LEN output
--Validation
IF LEN(@Name)=0
BEGIN
EXEC MessageHelper 'Name', @EMPTY,@MESSAGE OUTPUT --It will generate a common empty message.
RETURN @FAILED-- Readable Failed Return
END
IF LEN(@Name)<3
BEGIN
EXEC MessageHelper 'Name',3, @LEN,@MESSAGE OUTPUT --It will generate a common length check message.
RETURN @FAILED-- Readable Failed Return
END
--Data insertion
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
SELECT @LASTID=SCOPE_IDENTITY()
END TRY
BEGIN CATCH -- Error Traping Section
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED -- Readable Failed Return
END CATCH
RETURN @SUCCESSED -- Readable Successed Return
END
--End Customer_Set
--Start Customer_Get
CREATE PROCEDURE [dbo].[Customer_Get]
--Output values
@TOTAL_ROWS bigint OUTPUT,
@MESSAGE varchar(200) =NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON
--Variables
DECLARE @SUCCESSED int,
@FAILED int
DECLARE @EMPTY int,
@LEN int,
@NO_DATA_EXIST int
BEGIN TRY
--Get constraint value
EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT,@NO_DATA_EXIST OUTPUT
--Validation
IF (SELECT COUNT(CustomerId) FROM Customer )<= 0
BEGIN
EXEC MessageHelper '', @NO_DATA_EXIST,@MESSAGE OUTPUT --It will generate common no data exist message.
SELECT @TOTAL_ROWS=0
RETURN @SUCCESSED
END
--Data retrival
SELECT [CustomerId]
,[Name]
,[Address]
,[Mobile]
FROM [dbo].[Customer]
--Get total rows
SELECT @TOTAL_ROWS=@@ROWCOUNT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED
END CATCH
RETURN @SUCCESSED
END
--End Customer_Get
--Start Customer_DeleteById
CREATE PROCEDURE [dbo].[Customer_DeleteById]
--Input values
@CustomerId bigint,
@MESSAGE varchar(200) =NULL OUTPUT
AS
BEGIN
--Variables
DECLARE @SUCCESSED int,
@FAILED int
DECLARE @EMPTY int,
@LEN int
BEGIN TRY
--Get constraint value
EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT
--Validation
IF @@CustomerId <=0
BEGIN
EXEC MessageHelper 'Customer Id', @EMPTY,@MESSAGE OUTPUT
RETURN @FAILED
END
--Data deletion
DELETE FROM [dbo].[Customer]
WHERE (CustomerId = @CustomerId)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED
END CATCH
RETURN @SUCCESSED
END
--End Customer_DeleteById
