清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

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