SQL 框架代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL 框架
作者:Dezai.CN / 发布于2011/6/15/ 643
<div>--Start Customer_Set
CREATE PROCEDURE [dbo].[Customer_Set]
@Name as varchar(50),
 @Address as varchar(250), 
@Mobile as varchar(50)
AS
BEGIN</div>
<div>SET NOCOUNT ON</div>
<div>--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Name cannot be empty.',16,1)
END</div>
<div>IF LEN(@Name)<3
BEGIN
RAISERROR ('Name cannot be less than 3 characters.',16,1)
END</div>
<div>--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (0)
END CATCH</div>
<div>RETURN (1)
END
--End Customer_Set</div>
<div>
--Start Supplier_Set
CREATE PROCEDURE [dbo].[Supplier_Set]
@Name as varchar(50),
@Address as varchar(250), 
@Mobile as varchar(50)
AS
BEGIN</div>
<div>SET NOCOUNT ON</div>
<div>--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Please enter suppiler name.',16,1)
END</div>
<div>IF LEN(@Name)<3
BEGIN
RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
END</div>
<div>--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Supplier]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (-1)
END CATCH</div>
<div>RETURN (1)
END
--End Supplier_Set</div>
<div>--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</div>
SELECT @True=1
SELECT @False=0
END
--End GetValidationConstraint</div>
<div>
--Start ReturnMessage
CREATE PROCEDURE [dbo].[ReturnMessage]
--Success, Fail is the order of output parameter
@Success int OUTPUT,
@Fail int OUTPUT
AS
SET NOCOUNT ON</div>
<div>BEGIN
SELECT @Fail=0
SELECT @Success=1
END
--End ReturnMessage</div>
<div>--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)</div>
<div>
DECLARE @EMPTY int,
@LEN int,
@NO_DATA_EXIST int</div>
<div>DECLARE @SUCCESSED int,
@FAILED int</div>
<div>--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</div>
<div>--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</div>
<div>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</div>
<div>IF @ValidationConstraint = @NO_DATA_EXIST 
BEGIN 
SELECT @ValidationMessage = @NO_DATA_EXIST_MESSAGE
END
END
--End MessageHelper</div>
<div>--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</div>
<div>--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</div>
<div>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</div>
<div>RETURN @SUCCESSED -- Readable Successed Return
END
--End Customer_Set</div>
<div>--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</div>
<div>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]</div>
<div>--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</div>
<div>RETURN @SUCCESSED
END
--End Customer_Get</div>
<div>--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</div>
<div>DECLARE @EMPTY int,
@LEN int</div>
<div>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)</div>
<div>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</div>
<div>RETURN @SUCCESSED
END</div>
<div>--End Customer_DeleteById</div>
试试其它关键字
　SQL　　框架　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)