stored procedure issue

Question

All,

I am working on a procedure and I am kind of stuck at a point. I have pasted the code below. I understand the code can be improved a lot.. but, I need to see that it is covering all the requirements.

The issue I am facing is.. after I wrote this procedure I understood that I have to disable a constraint before I run this procedure and enable it after this procedure is executed. But I want to incorporate that alter table commands in this procedure itself. I wonder where do I put it in the code below. Another idea I had was to write the drop and creation of the constraints in a separate procedures and call them in this procedure.. but I am not sure how I can do it. These are novice ideas, that I have. I would appreciate if experts help me out. I also wanted to rollback the whole procedure, if at all any error occurred in any of the Begin- end blocks. any comments or suggestions on the code I would take as if I learnt something new :). I have not figured out yet how this code would affect the performance yet. The code that I have written below, propagates same id across different tables, which are connected through constraints. the effort was to minimize writing 6 insert statements explicitly into 6 different tables. Any help is appreciated.

CREATE PROCEDURE [dbo].[CREATEASSET_MODIFIED]
(

    @AliasName varchar(50),
    @Category varchar(50),
    @Corporatecode varchar(50),
    @id int output,
    @customer int, 
    @ReaderID int,
    @MAX_SUMMER decimal(3,2),
    @MIN_SUMMER decimal(3,2),
    @MAX_WINTER decimal(3,2),
    @MIN_WINTER decimal(3,2),
    @SummerStartDate date,
    @WinterStartDate date,
    @sign numeric(1,0)
)

AS

BEGIN 

SELECT  @id = ISNULL(max(IdentifiedObjectId),0) + 1 from CIM.IdentifiedObject

INSERT into CIM.IdentifiedObject 
VALUES (@id,@AliasName , '', '', @AliasName, '', @id, NULL)

IF @@ERROR <>0

BEGIN

PRINT 'Error HERE'

END

ELSE

PRINT 'INSERTED INTO IDENTIFIED OBJECT'

INSERT INTO CIM.AssetContainer VALUES (@id)

IF @@ERROR <>0

BEGIN

PRINT 'Error Occured HERE'

 END
ELSE

PRINT'INSERTED INTO ASSET CONTAINER'

INSERT INTO CIM.ASSET 
VALUES (@id,'1','VELCO',@Category,@corporatecode,1,
    null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)

IF @@ERROR <>0

BEGIN

PRINT 'Error Occured HERE'

END

ELSE

PRINT 'INSERTED INTO ASSET'

INSERT INTO CIM.EndDeviceAsset VALUES 
(NULL,1,0,1,1,1,1,1,1,1,null,@id,1,@CUSTOMER,1,10)

IF @@ERROR <>0

BEGIN

PRINT 'Error Occured HERE'

END

ELSE

PRINT 'INSERTED INTO ENDDEVICEASSET'
 INSERT INTO CIM.MeterAsset VALUES ( NULL,NULL,NULL,@id, 1)

IF @@ERROR <>0

BEGIN 

PRINT 'Error Occured HERE'
END
ELSE    
PRINT 'INSERTED INTO METERASSET'

INSERT INTO CIM_AUG.MeterAsset_Aug 
VALUES (@id,@ReaderID,@MAX_SUMMER,@MIN_SUMMER,@MAX_WINTER, 
    @MIN_WINTER, @SummerStartDate,@WinterStartDate,@sign)    

IF @@ERROR <>0

BEGIN  

PRINT 'Error Occured HERE'
END 

ELSE    

PRINT 'INSERTED INTO METERASSET_Aug '         

END

GO

-----------------------------------
execution of the procedure 
---------------------------------
declare @id int

exec dbo.CREATEASSET_M ASSET,UNIT,823,@id output,184,183,0,
    0,0,0,'12-1-2010','12-1-2010',1

print @id

Answer 1

OK, requirements are requirements, and if the circular reference must stay then here is how your procedure might look. The only things I needed to tweak are:

Add the statement disabling constraint before the try block
Add the statement enabling the constraint after the block
Add the @@trancount check before rolling back the tran
Move the line raising error after the try / catch block, because otherwise, there is no way to re-enable the constraint

Here is the abridged script:

create proc [dbo].[CREATEASSET_MODIFIED] 
( 
    @AliasName varchar(50), 
    @Category varchar(50), 
    @Corporatecode varchar(50), 
    @id int output, 
    @customer int, 
    @ReaderID int, 
    @MAX_SUMMER decimal(3,2), 
    @MIN_SUMMER decimal(3,2), 
    @MAX_WINTER decimal(3,2), 
    @MIN_WINTER decimal(3,2), 
    @SummerStartDate date, 
    @WinterStartDate date, 
    @sign numeric(1,0) 
) 
as

begin

    declare @errorNum int, @ErrorMessage varchar(200);

    alter table CIM.AssetContainer 
        nocheck constraint FK_AssetContainer_Asset;

    begin try

        begin tran;



        -- all your procedure statements, inserts etc...

        commit tran;

    end try
    begin catch

        if @@trancount > 0 rollback tran;

        select @errorNum = error_number(), @ErrorMessage = error_message();

    end catch;

    alter table CIM.AssetContainer 
        check constraint FK_AssetContainer_Asset;

    if @ErrorMessage is not null raiserror(@ErrorMessage, 16, 1);

end;
go

Oleg

Answer 2

@Oleg,

i kind of modified the code and now i am able to rollback the inserted records if anyerrors occur in the following way. But i still wonder how to tweak the constraints in the same procedures.

create PROCEDURE [dbo].[CREATEASSET_MODIFIED]
(
@AliasName varchar(50),
@Category varchar(50),
@Corporatecode varchar(50),
@id int output,
@customer int,
@ReaderID int,
@MAX_SUMMER decimal(3,2),
@MIN_SUMMER decimal(3,2),
@MAX_WINTER decimal(3,2),
@MIN_WINTER decimal(3,2),
@SummerStartDate date,
@WinterStartDate date,
@sign numeric(1,0)

)

AS
begin DECLARE @ErrorNum INT, @ErrorMessage varchar(200)

begin try                                                                                                                                                 
 BEGIN TRANSACTION                                                                                                                                

     SELECT @id = ISNULL(max(IdentifiedObjectId),0) + 1 from CIM.IdentifiedObject                                                                     

    INSERT into CIM.IdentifiedObject VALUES (@id,@AliasName , '', '', @AliasName, '', @id, NULL)                                                     


    INSERT INTO CIM.AssetContainer VALUES (@id)                                                                                                      

    INSERT INTO CIM.ASSET VALUES (@id,'1','VELCO',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)  

    INSERT INTO CIM.EndDeviceAsset VALUES (NULL,1,0,1,1,1,1,1,1,1,null,@id,1,@CUSTOMER,1,10)                                                         

    INSERT INTO CIM.MeterAsset VALUES ( NULL,NULL,NULL,@id, 1)                                                                                       

    INSERT INTO CIM_AUG.MeterAsset_Aug VALUES (@id,@ReaderID,@MAX_SUMMER,@MIN_SUMMER,@MAX_WINTER,@MIN_WINTER,@SummerStartDate,@WinterStartDate,@sign)
 COMMIT TRANSACTION                                                                                                                               
end try
begin catch
  Rollback Tran
  select @errorNum = Error_Number(),
         @ErrorMessage = Error_Message()
  RaisError(@ErrorMessage, 16, 1)
end catch

END

declare @id int

exec dbo.CREATEASSET_MODIFIED_Auyu ASSET,UNIT,823,@id output,182,182,0,0,0,0,'12-1-2010','12-1-2010',1

print @id

stored procedure issue

Follow this question

Related Questions