question

Katie 1 avatar image
Katie 1 asked

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
sql-server-2008t-sqlstored-procedures
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
1 Like 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).
1 Like 1 ·
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] ([assetID]) GO and again ALTER TABLE [cim].[Asset] WITH CHECK ADD CONSTRAINT [FK_AssetContainer] FOREIGN KEY([AssetContainer]) REFERENCES [dbo].[AssetContainer] ([assetContainerID]) GO
0 Likes 0 ·
@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.
0 Likes 0 ·
@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] ([assetID]) GO ALTER TABLE [cim].[Asset] WITH CHECK ADD CONSTRAINT [FK_AssetContainer] FOREIGN KEY([AssetContainer]) REFERENCES [cim].[AssetContainer] ([assetContainerID]) 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] ([assetID]) 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 !
0 Likes 0 ·
Show more comments
Oleg avatar image
Oleg answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Works Fine thank you so much!
0 Likes 0 ·
@Katie You are welcome. I am glad I could help.
0 Likes 0 ·
Katie 1 avatar image
Katie 1 answered
@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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Katie I added the answer. Please let me know if this works for you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.