CREATE
PROCEDURE
[dbo].[Customer_Set]
@
Name
as
varchar
(50),
@Address
as
varchar
(250),
@Mobile
as
varchar
(50)
AS
BEGIN
SET
NOCOUNT
ON
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
BEGIN
TRY
INSERT
INTO
[dbo].[Customer]
([
Name
]
,[Address]
,[Mobile])
VALUES
(@
Name
,@Address
,@Mobile)
END
TRY
BEGIN
CATCH
RETURN
(0)
END
CATCH
RETURN
(1)
END
CREATE
PROCEDURE
[dbo].[Supplier_Set]
@
Name
as
varchar
(50),
@Address
as
varchar
(250),
@Mobile
as
varchar
(50)
AS
BEGIN
SET
NOCOUNT
ON
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
BEGIN
TRY
INSERT
INTO
[dbo].[Supplier]
([
Name
]
,[Address]
,[Mobile])
VALUES
(@
Name
,@Address
,@Mobile)
END
TRY
BEGIN
CATCH
RETURN
(-1)
END
CATCH
RETURN
(1)
END
CRAETE
PROCEDURE
[dbo].[GetValidationConstraint]
@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
CREATE
PROCEDURE
[dbo].[ReturnMessage]
@Success
int
OUTPUT
,
@Fail
int
OUTPUT
AS
SET
NOCOUNT
ON
BEGIN
SELECT
@Fail=0
SELECT
@Success=1
END
CREATE
PROCEDURE
[dbo].[MessageHelper]
@Field
varchar
(200) =
NULL
,
@MinLenght
int
=
NULL
,
@ValidationConstraint
int
,
@ValidationMessage
varchar
(200)
OUTPUT
AS
BEGIN
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
SET
@EMPTY_MESSAGE =
'cannot be empty.'
SET
@MINIMUM_LENGHT_MESSAGE =
'cannot be less than'
SET
@NO_DATA_EXIST_MESSAGE =
'No record found.'
EXEC
ReturnMessage @SUCCESSED
output
, @FAILED
output
EXEC
GetValidationConstraint @EMPTY
OUTPUT
, @LEN
OUTPUT
, @NO_DATA_EXIST
OUTPUT
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
CREATE
PROCEDURE
[dbo].[Customer_Set]
@
Name
as
varchar
(50),
@Address
as
varchar
(250),
@Mobile
as
varchar
(50),
@LASTID
bigint
OUTPUT
,
@MESSAGE
varchar
(200) =
NULL
OUTPUT
AS
SET
NOCOUNT
ON
BEGIN
DECLARE
@SUCCESSED
int
,
@FAILED
int
DECLARE
@EMPTY
int
,
@LEN
int
BEGIN
TRY
EXEC
ReturnMessage @SUCCESSED
output
, @FAILED
output
EXEC
GetValidationConstraint @EMPTY
output
, @LEN
output
IF LEN(@
Name
)=0
BEGIN
EXEC
MessageHelper
'Name'
, @EMPTY,@MESSAGE
OUTPUT
RETURN
@FAILED
END
IF LEN(@
Name
)<3
BEGIN
EXEC
MessageHelper
'Name'
,3, @LEN,@MESSAGE
OUTPUT
RETURN
@FAILED
END
INSERT
INTO
[dbo].[Customer]
([
Name
]
,[Address]
,[Mobile])
VALUES
(@
Name
,@Address
,@Mobile)
SELECT
@LASTID=SCOPE_IDENTITY()
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
CREATE
PROCEDURE
[dbo].[Customer_Get]
@TOTAL_ROWS
bigint
OUTPUT
,
@MESSAGE
varchar
(200) =
NULL
OUTPUT
AS
BEGIN
SET
NOCOUNT
ON
DECLARE
@SUCCESSED
int
,
@FAILED
int
DECLARE
@EMPTY
int
,
@LEN
int
,
@NO_DATA_EXIST
int
BEGIN
TRY
EXEC
ReturnMessage @SUCCESSED
OUTPUT
, @FAILED
OUTPUT
EXEC
GetValidationConstraint @EMPTY
OUTPUT
, @LEN
OUTPUT
,@NO_DATA_EXIST
OUTPUT
IF (
SELECT
COUNT
(CustomerId)
FROM
Customer )<= 0
BEGIN
EXEC
MessageHelper
''
, @NO_DATA_EXIST,@MESSAGE
OUTPUT
SELECT
@TOTAL_ROWS=0
RETURN
@SUCCESSED
END
SELECT
[CustomerId]
,[
Name
]
,[Address]
,[Mobile]
FROM
[dbo].[Customer]
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
CREATE
PROCEDURE
[dbo].[Customer_DeleteById]
@CustomerId
bigint
,
@MESSAGE
varchar
(200) =
NULL
OUTPUT
AS
BEGIN
DECLARE
@SUCCESSED
int
,
@FAILED
int
DECLARE
@EMPTY
int
,
@LEN
int
BEGIN
TRY
EXEC
ReturnMessage @SUCCESSED
OUTPUT
, @FAILED
OUTPUT
EXEC
GetValidationConstraint @EMPTY
OUTPUT
, @LEN
OUTPUT
IF @@CustomerId <=0
BEGIN
EXEC
MessageHelper
'Customer Id'
, @EMPTY,@MESSAGE
OUTPUT
RETURN
@FAILED
END
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