ホームページ >データベース >mysql チュートリアル >SQL 框架,完全用 SQL 写的
无详细内容 无 --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