SQL 框架，完全用 SQL 写的

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

--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.',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),@Mobile as varchar(50)
AS
BEGIN

	SET NOCOUNT ON

	--Validation
	IF @Name IS NULL
	BEGIN
		RAISERROR ('Please enter suppiler name.',1)
	END

	IF LEN(@Name)<3
	BEGIN
		RAISERROR ('Supplier name cannot be less than 3 characters.',1)
	END	

	--Data Insertion
	BEGIN TRY
		INSERT INTO [dbo].[Supplier]
			([Name],@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',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',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),@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],[Mobile])
		VALUES
           (@Name,@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,@NO_DATA_EXIST int			
	
	BEGIN TRY
		--Get constraint value
		EXEC ReturnMessage @SUCCESSED OUTPUT,@FAILED OUTPUT						
		EXEC GetValidationConstraint @EMPTY 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],[Mobile]		  
		FROM [dbo].[Customer]			

		--Get total rows	
		SELECT @[email?protected]@ROWCOUNT
		 
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage nvarchar(4000);
		DECLARE @ErrorSeverity int;
		DECLARE @ErrorState int;
		
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),@ErrorState = ERROR_STATE();		
		
		RAISERROR (@ErrorMessage,@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,@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',@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(),@ErrorState);
		RETURN @FAILED
	END CATCH

	RETURN @SUCCESSED
END

--End Customer_DeleteById

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：东莞站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!