x

stored procedure issue

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

asked Aug 18, 2010 at 10:23 AM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

@Katie Could you please elaborate why do you need to disable a constraint and then re-enable it within the body of the procedure? Generally speaking, this is not a good option, specifically when your tables grow in size enough so re-enabling a constraint becomes a very time-consumming process. Maybe there are other ways to tackle your problem, but it would be nice if you provided more information. Thank you.
Aug 18, 2010 at 10:44 AM Oleg

Oleg, there is this table called asset container,which has only one column called asset container id . Something like this.

ALTER TABLE [cim].[AssetContainer] WITH CHECK ADD CONSTRAINT [FK_AssetContainer_Asset] FOREIGN KEY([assetContainerID]) REFERENCES [dbo].Asset GO

and again

ALTER TABLE [cim].[Asset] WITH CHECK ADD CONSTRAINT [FK_AssetContainer] FOREIGN KEY([AssetContainer]) REFERENCES [dbo].AssetContainer GO
Aug 18, 2010 at 11:11 AM Katie 1

@Katie So, you have cim.Asset references dbo.AssetContainer and cim.AssetContaier references dbo.Asset (4 tables), right?

You query the last IdentifiedObjectId in IdentifiedObject table, increment it by 1 and then insert incremented value back into IdentifiedObject. The problem now is that when you try to insert that incremented @id value as a value of assetContainerID column of the cim.AssetContainer table, this value has to exist as a valid AssetID of existing record in dbo.Asset table, and this will always cause the trouble if such record does not yet exist. Even if you disable FK_AssetContainer constraint at this point, it is not going to solve your problem because in the end you will have to re-enable it and this will fail if there is no record with assetID = @id in the dbo.Asset table. Since your procedure does not do anything to dbo.Asset and dbo.AssetContainer tables, please give a little bit more information about the role of the IdentifiedObject table and the purpose of the cim schema.
Aug 18, 2010 at 11:49 AM Oleg

@oleg, Firstly, thank you for being soo patient with me to understand my problem. Sorry for the confusion. there are only two tables cim.asset and cim.assetcontainer.

cim.asset table has (assetid, assetcontianerid) cim.assetcontainer has (assetcontainerID)

ALTER TABLE [cim].[AssetContainer] WITH CHECK ADD CONSTRAINT [FK_AssetContainer_Asset] FOREIGN KEY([assetContainerID]) REFERENCES [cim].Asset GO

ALTER TABLE [cim].[Asset] WITH CHECK ADD CONSTRAINT [FK_AssetContainer] FOREIGN KEY([AssetContainer]) REFERENCES [cim].AssetContainer GO

for now to get my procedure working, i have to asssume that the objectidentifierid = assetid = assetcontainerid

for this to happend unless i drop the constraint below, i cannot insert the data. ALTER TABLE [cim].[AssetContainer] WITH CHECK ADD CONSTRAINT [FK_AssetContainer_Asset] FOREIGN KEY([assetContainerID]) REFERENCES [cim].Asset GO

i am not worrying about the performance because.. any at given time there are only few hundreds of records in these tables. and only once few months or a year one record is inserted.

Thank you so much !
Aug 18, 2010 at 12:20 PM Katie 1
@Katie But if you only have 2 tables then your constraints cause circular reference, and thus, there is no way to insert data in either of these 2 tables. If you try to insert into Asset first then you cannot because the assetcontianerid value is not valid in AssetContainer table yet, but if you instead try to insert into AssetContainer then you cannot because the AssetContainerID is not valid existing AssetID column of the Asset table. This makes it look as what you really need is to ensure that whenever the record is inserted into one of these tables, the other needs to have a record inserted too. If I am correct, please let me know which of the 2 tables you would consider as a parent table, and then we can figure out which constraints to toggle so your procedure starts working, and after that we can toggle the proc itself to ensure that it behaves like you want (either inserts all records or inserts none and rolls back).
Aug 18, 2010 at 12:35 PM Oleg
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

answered Aug 18, 2010 at 02:51 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Works Fine thank you so much!
Aug 19, 2010 at 06:56 AM Katie 1
@Katie You are welcome. I am glad I could help.
Aug 19, 2010 at 08:54 AM Oleg
(comments are locked)
10|1200 characters needed characters left

@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
more ▼

answered Aug 18, 2010 at 12:50 PM

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

@Katie I added the answer. Please let me know if this works for you.
Aug 18, 2010 at 02:53 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x985
x407

asked: Aug 18, 2010 at 10:23 AM

Seen: 1664 times

Last Updated: Aug 18, 2010 at 10:41 AM