返回顶部

收藏

SQL 框架,完全用 SQL 写的

更多
--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

标签:sql

收藏

0人收藏

支持

1

反对

0