存储过程异常处理

USE [CEDB_Dimension]
GO
/****** Object: StoredProcedure [dbo].[sptbDimensions_Add] Script Date: 10/07/2014 12:47:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptbDimensions_Add]
@ParentID uniqueidentifier,
@DimensionsName nvarchar(64),
@DimensionName nvarchar(64),
@DimensionType int,
@DimensionValueName nvarchar(64),
@DimensionValueCode varchar(512),
@Precision int,
@FrequencyCode int,
@DatabaseCode varchar(512),
@DatabaseID int,
@fdUpdateID varchar(32),
@fdUpdateName nvarchar(32),
@fdExplanation NVARCHAR(max),
@vDimensionID uniqueidentifier
AS
BEGIN
DECLARE @fdUpdateTime datetime
SET @fdUpdateTime=GETDATE()

DECLARE @vDimensionsID uniqueidentifier=NEWID()

DECLARE @vDimensionValueNodeID uniqueidentifier=NEWID()
DECLARE @prePath varchar(512)
DECLARE @preXIndex int
DECLARE @sql NVARCHAR(max)
SET NOCOUNT ON;
BEGIN TRAN
BEGIN TRY
EXEC spCreateDBTable @DimensionsID=@vDimensionsID;

SELECT @prePath=fdNodePath+'/'+CAST(@vDimensionsID as varchar(36)) FROM tbPreTree WHERE fdNodeID=@ParentID
SELECT @preXIndex=ISNULL(MAX(fdXindex),0)+1 FROM tbPreTree WHERE fdParentID=@ParentID
--写入测试树表
INSERT INTO tbPreTree(fdNodeID,fdDatabaseID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateID,fdUpdateName,fdUpdateTime)
VALUES(@vDimensionsID,@DatabaseID,@prePath,@ParentID,1,@DimensionsName,@preXIndex,@fdUpdateID,@fdUpdateName,@fdUpdateTime)
--写入维度集表
INSERT INTO tbDimensions(fdID,fdName,fdAlias,fdPrecision,fdFrequencyCode,fdDatabaseCode,fdUpdateID,fdUpdateName,fdUpdateTime,fdExplanation)
VALUES(@vDimensionsID,@DimensionsName,'',@Precision,@FrequencyCode,@DatabaseCode,@fdUpdateID,@fdUpdateName,@fdUpdateTime,@fdExplanation)
--写入维度表
SET @sql='INSERT INTO '+'[tbD'+CAST(@vDimensionsID as varchar(36)) +'] '+'(fdDimensionID,fdDimensionName,fdDimensionType,fdXIndex)'
SET @sql=@sql+' VALUES('''+CAST(@vDimensionID as varchar(36))+''','''+@DimensionName+''','''+CAST(@DimensionType as varchar(36))+''',1)'
PRINT @sql
EXEC(@sql)
--INSERT INTO tbDimension(fdDimensionsID,fdDimensionID,fdDimensionName,fdDimensionType,fdXIndex)
--VALUES(@vDimensionsID,@vDimensionID,@DimensionName,@DimensionType,1)
--写入维度值树表
SET @sql='INSERT INTO '+'[tbDV'+CAST(@vDimensionsID as varchar(36)) +'] '+'(fdNodeID,fdNodePath,fdValueCode,fdName,fdDimensionID,fdXIndex,fdUpdateID,fdUpdateName,fdUpdateTime)'
SET @sql=@sql+' VALUES('''+CAST(@vDimensionValueNodeID as varchar(36))+''',''/'+CAST(@vDimensionValueNodeID as varchar(36))+''','''+@DimensionValueCode+''','''+@DimensionValueName+''','''+CAST(@vDimensionsID as varchar(36))+''',1,'''+@fdUpdateID+''','''+@fdUpdateName+''','''+CAST(@fdUpdateTime as varchar(36))+''')'
PRINT @sql
EXEC(@sql)
--INSERT INTO tbDimensionValue(fdNodeID,fdNodePath,fdValueCode,fdName,fdDimensionID,fdXIndex,fdUpdateID,fdUpdateName,fdUpdateTime)
--VALUES(@vDimensionValueNodeID,'/'+CAST(@vDimensionValueNodeID as varchar(36)),@DimensionValueCode,@DimensionValueName,@vDimensionID,1,@fdUpdateID,@fdUpdateName,@fdUpdateTime)

IF @@ERROR=0
BEGIN
COMMIT TRAN
RETURN 1
END
ELSE
BEGIN
ROLLBACK TRAN
--返回结果为真
RETURN 0
END
END try
BEGIN CATCH
print 'catch'
print Error_Line()
print Error_Message()
print Error_number()

IF XACT_STATE()<>0
BEGIN
ROLLBACK TRAN
--返回结果为假
RETURN 0
END
END CATCH

END

 

 

---

 

 

USE [CEDB_Dimension]
GO
/****** Object: StoredProcedure [dbo].[sptbTree_tbTreeToPre_WriteData] Script Date: 10/07/2014 12:48:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sptbTree_tbTreeToPre_WriteData]
@tbDimensions Dimensions READONLY,
@tbDimensionValue DimensionValue READONLY,
@tbDimension Dimension READONLY,
@tbDimensionWithSequenceRelation DimensionWithSequenceRelation READONLY,
--@tbDimensionWithSequence DimensionWithSequence READONLY,
--@tbSequenceNotInDimensions tbSequenceNotInDimensions READONLY,
-- @tbUnit unit READONLY,
@tbPreTree Tree READONLY,
@orgNodeID uniqueidentifier,
@descNodeID UNIQUEIDENTIFIER,
@tbAggregate Aggregate READONLY,
@tbAggregateDimension AggregateDimension READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fdUpdateTime datetime=getdate()
BEGIN TRAN
BEGIN TRY
DECLARE @count int
SET @count=0

select @count=COUNT(1) from @tbDimensions
if @count>0
begin
--写入维度集
INSERT INTO tbDimensions(fdID,fdName,fdAlias,fdPrecision,fdFrequencyCode,fdDatabaseCode,fdUpdateID,fdUpdateName,fdUpdateTime,fdExtractTime,fdExplanation)
SELECT fdID,fdName,fdAlias,fdPrecision,fdFrequencyCode,fdDatabaseCode,fdUpdateID,fdUpdateName,GETDATE(),GETDATE(),fdExplanation FROM @tbDimensions
--写入维度
insert into tbDimension(fdDimensionsID,fdDimensionID,fdDimensionName,fdDimensionType,fdXIndex)
select fdDimensionsID,fdDimensionID,fdDimensionName,fdDimensionType,fdXIndex from @tbDimension
--写入维度值
insert into tbDimensionValue(fdNodeID,fdNodePath,fdParentID,fdValueCode,fdName,fdDimensionID,fdXIndex,fdUpdateID,fdUpdateName,fdUpdateTime)
select fdNodeID,fdNodePath,fdParentID,fdValueCode,fdName,fdDimensionID,fdXIndex,fdUpdateID,fdUpdateName,GETDATE() from @tbDimensionValue
--写入测试树
insert into tbPreTree(fdNodeID,fdDatabaseID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateID,fdUpdateName,fdUpdateTime)
select fdNodeID,fdDatabaseID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateID,fdUpdateName,GETDATE() from @tbPreTree
--写入维度值序列
SELECT fdSequenceID,dwsr.fdNewDimensionValueName,dwsr.fdNewDimensionID INTO #tbDimensionWithSequence FROM tbDimensionWithSequence AS dws
INNER JOIN @tbDimensionWithSequenceRelation AS dwsr ON dws.fdDimensionID=dwsr.fdOldDimensionID AND dws.fdDimensionValueName=dwsr.fdOldDimensionValueName
INSERT INTO tbDimensionWithSequence( fdSequenceID,fdDimensionValueName,fdDimensionID,fdUpdateTime)
SELECT DISTINCT fdSequenceID,fdNewDimensionValueName,fdNewDimensionID,GETDATE() FROM #tbDimensionWithSequence
DROP TABLE #tbDimensionWithSequence
--INSERT INTO tbDimensionWithSequence( fdSequenceID,fdDimensionValueName,fdIndicatorCode,fdIndicatorUnit,fdDimensionID,fdUpdateTime)
--SELECT fdSequenceID,fdDimensionValueName,fdIndicatorCode,fdIndicatorUnit,fdDimensionID,GETDATE() FROM @tbDimensionWithSequence
--写入不包含
INSERT INTO tbSequenceNotInDimensions(fdSequenceID,fdDimensionsID,fdUpdateTime)
SELECT fdSequenceID,@descNodeID,GETDATE() FROM tbSequenceNotInDimensions
WHERE fdDimensionsID=@orgNodeID
--INSERT INTO tbSequenceNotInDimensions(fdSequenceID,fdDimensionsID,fdUpdateTime)
--SELECT fdSequenceID,fdDimensionsID,GETDATE() FROM @tbSequenceNotInDimensions
--写入unit
--INSERT INTO tbUnit(fdIndicatorCode,fdIndicatorName,fdIndicatorUnit,fdLastUnit,fdCurUnit,fdIsUnitDiffer,fdDimensionsID,fdUpdateTime)
--SELECT fdIndicatorCode,fdIndicatorName,fdIndicatorUnit,fdLastUnit,fdCurUnit,fdIsUnitDiffer,fdDimensionsID,GETDATE() FROM @tbUnit
INSERT INTO tbUnit(fdIndicatorCode,fdIndicatorName,fdIndicatorUnit,fdIsUnitDiffer,fdDimensionsID,fdUpdateTime)
SELECT DISTINCT fdIndicatorCode,fdIndicatorName,fdIndicatorUnit,fdIsUnitDiffer,@descNodeID,GETDATE() FROM dbo.tbUnit
WHERE fdDimensionsID=@orgNodeID
--写入维度集序列
INSERT INTO dbo.tbSequenceInDimensions(fdDimensionsID,fdSequenceID,fdDatabaseCode,fdBasicSequenceID ,fdDisplayName ,fdIndicatorCode,fdAreaCode ,fdAreaName, fdIndicatorUnit)
SELECT @descNodeID,fdSequenceID,fdDatabaseCode,fdBasicSequenceID ,fdDisplayName ,fdIndicatorCode,fdAreaCode ,fdAreaName, fdIndicatorUnit FROM dbo.tbSequenceInDimensions WHERE fdDimensionsID=@orgNodeID
--写入统计
INSERT INTO tbAggregate( fdAggregateID,fdDimensionsID ,fdAggregateType ,fdAggregateCode ,fdAggregateName ,fdAggregateUnit ,fdAggregateFormula ,fdParticipateDimension)
SELECT fdAggregateID,@descNodeID ,fdAggregateType ,fdAggregateName ,fdAggregateCode ,fdAggregateUnit , fdAggregateFormula ,fdParticipateDimension FROM @tbAggregate
--写入统计维度
INSERT INTO dbo.tbAggregateDimension( fdAggregateID ,fdDimensionValueID ,fdDimensionID ,fdIsParticipate)
SELECT fdAggregateID ,fdDimensionValueID ,fdDimensionID ,fdIsParticipate FROM @tbAggregateDimension

end
IF @@ERROR=0
BEGIN
COMMIT TRAN
return 1
END
ELSE
BEGIN
ROLLBACK TRAN
return 0
END
END TRY
BEGIN CATCH
IF XACT_STATE()<>0
ROLLBACK TRAN
--返回结果为假
select ERROR_LINE() AS ErrorLine,Error_Message(),Error_number()
return Error_number()

END CATCH
--返回结果为真
END