Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Resource deadlock

Author  Topic 

Kenny Blankenship
Starting Member

25 Posts
Posted - 2004-10-01 : 20:38:24
I'm not sure where this belongs but, I have 2 sprocs that select and update two to three tables. Periodically one of them will fail because of a resource deadlock. Each sproc executes on 750 to 1000ms interval, they are part of a 'real-time' MES system. When I initially set them up, they failed quite frequently. After running a trace, it seemed that they were taking too long to execute, so I re-wrote them to improve their execution speed. I also added NOLOCK to just about every statement that I could. This reduced the failures from a dozen per hour to a dozen per shift. While I wrote the transactions to tolerate the failures, I am interested in eliminating the failures and better understanding how to avoid this issue. Any suggestions?

Thanks in advance, Sean

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2004-10-01 : 21:13:27
provide us with DDL for the underlying tables and indexes and views. Are there triggers on any tables? we'll need that DDL too.

Please also post the DDL for the sprocs you are having a problem with.

We should be able to help you make improvements, we just need to see what you have right now.



-ec
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-02 : 10:44:11
you should determine which one is locking the resources, what resources is being locked, if they're sharing these resources and determine which should be executed first or priority. if there's no sequencing then improve the sprocs and benchmark the running time so that if the sproc exceeds the expected running time, you may want to prevent the other sproc from running or prioritize the sproc.
Go to Top of Page

Kenny Blankenship
Starting Member

25 Posts
Posted - 2004-10-02 : 18:33:39
Thanks for the advice. There are no triggers or views on the tables. I don't have access to the DB at the moment, but I will provide the additional data needed on Monday.

I am not a DB admin or developer by trade, so I am a little lost on some of the diagnostic tools available. I ran the trace and it clearly shows the execution times as well as the resource deadlock, but other than that, it seems limited. I put error trapping in the sproc, but when there is a resource deadlock, it occurs when the sproc is called, not during its execution. This kind of baffles me since my sproc is very simple, unless a resource deadlock kills the entire sproc and is not captured by @@error.

Hopefully the additional information that I provide on Monday will add clarity to the problem.
Go to Top of Page

Kenny Blankenship
Starting Member

25 Posts
Posted - 2004-10-06 : 07:50:26
As promised, here's the code. It's rather lengthy, my apologizes, but repeatative.

A little background info, the two procedures query the same table. The table contains product information used to fullfill customer's orders. The customer's orders are released onto a conveyor and automatically loaded onto a pallet for delivery, one big vending machine.

CREATE PROCEDURE vp_ReleaseByIntersection

	@RSID_01 INT OUT, @RSID_02 INT OUT, @RSID_03 INT OUT, @RSID_04 INT OUT,  @RSID_05 INT OUT,
	@RSID_06 INT OUT, @RSID_07 INT OUT,

	@Size_01 INT OUT, @Size_02 INT OUT, @Size_03 INT OUT, @Size_04 INT OUT, @Size_05 INT OUT,
	@Size_06 INT OUT, @Size_07 INT OUT,

	@SKU_01 INT OUT, @SKU_02 INT OUT, @SKU_03 INT OUT, @SKU_04 INT OUT, @SKU_05 INT OUT,
	@SKU_06 INT OUT, @SKU_07 INT OUT,

	@PSID_01 INT OUT, @PSID_02 INT OUT, @PSID_03 INT OUT, @PSID_04 INT OUT, @PSID_05 INT OUT,
	@PSID_06 INT OUT, @PSID_07 INT OUT, 

	@RLID_01 INT OUT, @RLID_02 INT OUT, @RLID_03 INT OUT, @RLID_04 INT OUT, @RLID_05 INT OUT,
	@RLID_06 INT OUT, @RLID_07 INT OUT,

	@LRID_01 INT, @LRID_02 INT, @LRID_03 INT, @LRID_04 INT, @LRID_05 INT,
	@LRID_06 INT, @LRID_07 INT,

	@Desc_01 VARCHAR(50) OUT, @Desc_02 VARCHAR(50) OUT, @Desc_03 VARCHAR(50) OUT, @Desc_04 VARCHAR(50) OUT,  @Desc_05 VARCHAR(50) OUT,
	@Desc_06 VARCHAR(50) OUT, @Desc_07 VARCHAR(50) OUT,

	@Request INT, 

	@TriggerMerge BIT OUT,
	@TriggerDivert BIT OUT
AS
BEGIN

	DECLARE @PLID INT, @Request_Mask INT, @Trans_Error INT
	
	SET @Trans_Error = 0

	IF @TriggerMerge = 1
	BEGIN
		SET @Request_Mask = POWER(2, 1)
		SET @PLID = POWER(2,0)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_01
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_01 = RSID, @SKU_01 = SKU, @Size_01 = SCID, @PSID_01 = PSID, @RLID_01 = CID
			FROM 		tblReleaseSequencing 
			WITH 		(NOLOCK)
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_01 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_01
			
		END
	
		IF @RSID_01 = 0 OR @RSID_01 = NULL
		BEGIN
			SET @RSID_01 = -1
			SET @SKU_01 = -1
			SET @Size_01 = -1
			SET @PSID_01 = -1
			SET @RLID_01 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 1' )
		
		SET @Request_Mask = POWER(2, 2)
		SET @PLID = POWER(2,1)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_02
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_02 = RSID, @SKU_02 = SKU, @Size_02 = SCID, @PSID_02 = PSID, @RLID_02 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_02 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_02
		END

		IF @RSID_02 = 0 OR @RSID_02 = NULL
		BEGIN
			SET @RSID_02 = -1
			SET @SKU_02 = -1
			SET @Size_02 = -1
			SET @PSID_02 = -1
			SET @RLID_02 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 2' )

		SET @Request_Mask = POWER(2, 3)
		SET @PLID = POWER(2,2)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_03
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_03 = RSID, @SKU_03 = SKU, @Size_03 = SCID, @PSID_03 = PSID, @RLID_03 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_03 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_03
		END

		IF @RSID_03 = 0 OR @RSID_03 = NULL
		BEGIN
			SET @RSID_03 = -1
			SET @SKU_03 = -1
			SET @Size_03 = -1
			SET @PSID_03 = -1
			SET @RLID_03 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 3' )

		SET @Request_Mask = POWER(2, 4)
		SET @PLID = POWER(2,3)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_04
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_04 = RSID, @SKU_04 = SKU, @Size_04 = SCID, @PSID_04 = PSID, @RLID_04 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_04 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_04
		END

		IF @RSID_04 = 0 OR @RSID_04 = NULL
		BEGIN
			SET @RSID_04 = -1
			SET @SKU_04 = -1
			SET @Size_04 = -1
			SET @PSID_04 = -1
			SET @RLID_04 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 4' )

		SET @Request_Mask = POWER(2, 5)
		SET @PLID = POWER(2,4)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_05
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_05 = RSID, @SKU_05 = SKU, @Size_05 = SCID, @PSID_05 = PSID, @RLID_05 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))
	
			SELECT 	@Desc_05 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_05
		END

		IF @RSID_05 = 0 OR @RSID_05 = NULL
		BEGIN
			SET @RSID_05 = -1
			SET @SKU_05 = -1
			SET @Size_05 = -1
			SET @PSID_05 = -1
			SET @RLID_05 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 5' )

		SET @Request_Mask = POWER(2, 6)
		SET @PLID = POWER(2,5)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_06
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_06 = RSID, @SKU_06 = SKU, @Size_06 = SCID, @PSID_06 = PSID, @RLID_06 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_06 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_06
		END

		IF @RSID_06 = 0 OR @RSID_06 = NULL
		BEGIN
			SET @RSID_06 = -1
			SET @SKU_06 = -1
			SET @Size_06 = -1
			SET @PSID_06 = -1
			SET @RLID_06 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 6' )

		SET @Request_Mask = POWER(2, 7)
		SET @PLID = POWER(2,6)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_07
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_07 = RSID, @SKU_07 = SKU, @Size_07 = SCID, @PSID_07 = PSID, @RLID_07 = CID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing
						WITH (NOLOCK)  
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_07 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_07
		END

		IF @RSID_07 = 0 OR @RSID_07 = NULL
		BEGIN
			SET @RSID_07 = -1
			SET @SKU_07 = -1
			SET @Size_07 = -1
			SET @PSID_07 = -1
			SET @RLID_07 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Merge 7' )
	
		SET @TriggerMerge = 0
	END

	IF @TriggerDivert = 1
	BEGIN
		SET @Request_Mask = POWER(2, 1)
		SET @PLID = POWER(2,16)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_01
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_01 = RSID, @SKU_01 = SKU, @Size_01 = SCID, @PSID_01 = PSID, @RLID_01 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_01 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_01
		END

		IF @RSID_01 = 0 OR @RSID_01 = NULL
		BEGIN
			SET @RSID_01 = -1
			SET @SKU_01 = -1
			SET @Size_01 = -1
			SET @PSID_01 = -1
			SET @RLID_01 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 1' )

		SET @Request_Mask = POWER(2, 2)
		SET @PLID = POWER(2,17)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_02
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_02 = RSID, @SKU_02 = SKU, @Size_02 = SCID, @PSID_02 = PSID, @RLID_02 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_02 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_02
		END

		IF @RSID_02 = 0 OR @RSID_02 = NULL
		BEGIN
			SET @RSID_02 = -1
			SET @SKU_02 = -1
			SET @Size_02 = -1
			SET @PSID_02 = -1
			SET @RLID_02 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 2' )

		SET @Request_Mask = POWER(2, 3)
		SET @PLID = POWER(2,18)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_03
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_03 = RSID, @SKU_03 = SKU, @Size_03 = SCID, @PSID_03 = PSID, @RLID_03 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_03 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_03
		END

		IF @RSID_03 = 0 OR @RSID_03 = NULL
		BEGIN
			SET @RSID_03 = -1
			SET @SKU_03 = -1
			SET @Size_03 = -1
			SET @PSID_03 = -1
			SET @RLID_03 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 3' )

		SET @Request_Mask = POWER(2, 4)
		SET @PLID = POWER(2,19)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_04
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_04 = RSID, @SKU_04 = SKU, @Size_04 = SCID, @PSID_04 = PSID, @RLID_04 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_04 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_04
		END

		IF @RSID_04 = 0 OR @RSID_04 = NULL
		BEGIN
			SET @RSID_04 = -1
			SET @SKU_04 = -1
			SET @Size_04 = -1
			SET @PSID_04 = -1
			SET @RLID_04 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 4' )

		SET @Request_Mask = POWER(2, 5)
		SET @PLID = POWER(2,20)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_05
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_05 = RSID, @SKU_05 = SKU, @Size_05 = SCID, @PSID_05 = PSID, @RLID_05 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_05 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_05
		END

		IF @RSID_05 = 0 OR @RSID_05 = NULL
		BEGIN
			SET @RSID_05 = -1
			SET @SKU_05 = -1
			SET @Size_05 = -1
			SET @PSID_05 = -1
			SET @RLID_05 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 5' )

		SET @Request_Mask = POWER(2, 6)
		SET @PLID = POWER(2,21)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_06
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_06 = RSID, @SKU_06 = SKU, @Size_06 = SCID, @PSID_06 = PSID, @RLID_06 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (MDID & @PLID <> 0) AND 
							 (PLID & @PLID = 0)))

			SELECT 	@Desc_06 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_06
		END

		IF @RSID_06 = 0 OR @RSID_06 = NULL
		BEGIN
			SET @RSID_06 = -1
			SET @SKU_06 = -1
			SET @Size_06 = -1
			SET @PSID_06 = -1
			SET @RLID_06 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 6' )

		SET @Request_Mask = POWER(2, 7)
		SET @PLID = POWER(2,22)

		UPDATE tblReleaseSequencing SET PLID = (PLID|@PLID)  WHERE RSID =  @LRID_07
		
		IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
		BEGIN 
			SELECT 	@RSID_07 = RSID, @SKU_07 = SKU, @Size_07 = SCID, @PSID_07 = PSID, @RLID_07 = DID
			FROM 		tblReleaseSequencing
			WITH 		(NOLOCK) 
			WHERE 	(MDID & @PLID <> 0) AND 
					(PLID & @PLID = 0) AND 
					(Released = 0) AND
					(DID = 101 OR DID = 201) AND 
					(RSID = (SELECT MIN(RSID) 
						FROM tblReleaseSequencing 
						WITH (NOLOCK) 
						WHERE (PLID & @PLID = 0) AND
						(DID = 101 OR DID = 201 OR DID = 1)))

			SELECT 	@Desc_07 = Description 
			FROM		tblProductList 
			WHERE	SKU = @SKU_07
		END
		IF @RSID_07 = 0 OR @RSID_07 = NULL
		BEGIN
			SET @RSID_07 = -1
			SET @SKU_07 = -1
			SET @Size_07 = -1
			SET @PSID_07 = -1
			SET @RLID_07 = -1
		END

		IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByIntersection - Divert 7' )

		SET @TriggerDivert = 0
	END
END
GO

CREATE PROCEDURE vp_ReleaseByCell
	
	@SKU_01 INT OUT, @SKU_02 INT OUT, @SKU_03 INT OUT, @SKU_04 INT OUT, @SKU_05 INT OUT,
	@SKU_06 INT OUT, @SKU_07 INT OUT, @SKU_08 INT OUT, @SKU_09 INT OUT, @SKU_10 INT OUT,

	@RSID_01 INT OUT, @RSID_02 INT OUT, @RSID_03 INT OUT, @RSID_04 INT OUT,  @RSID_05 INT OUT,
	@RSID_06 INT OUT, @RSID_07 INT OUT, @RSID_08 INT OUT, @RSID_09 INT OUT,  @RSID_10 INT OUT,

	@PT_01 INT OUT, @PT_02 INT OUT, @PT_03 INT OUT, @PT_04 INT OUT, @PT_05 INT OUT,
	@PT_06 INT OUT, @PT_07 INT OUT, @PT_08 INT OUT, @PT_09 INT OUT, @PT_10 INT OUT,

	@TID_01 INT OUT, @TID_02 INT OUT, @TID_03 INT OUT, @TID_04 INT OUT, @TID_05 INT OUT,
	@TID_06 INT OUT, @TID_07 INT OUT, @TID_08 INT OUT, @TID_09 INT OUT, @TID_10 INT OUT,

	@LRID_01 INT, @LRID_02 INT, @LRID_03 INT, @LRID_04 INT,  @LRID_05 INT,
	@LRID_06 INT, @LRID_07 INT, @LRID_08 INT, @LRID_09 INT,  @LRID_10 INT,
	
	@Desc_01 VARCHAR(50) OUT, @Desc_02 VARCHAR(50) OUT, @Desc_03 VARCHAR(50) OUT, @Desc_04 VARCHAR(50) OUT,  @Desc_05 VARCHAR(50) OUT,
	@Desc_06 VARCHAR(50) OUT, @Desc_07 VARCHAR(50) OUT, @Desc_08 VARCHAR(50) OUT, @Desc_09 VARCHAR(50) OUT,  @Desc_10 VARCHAR(50) OUT,

	@Request INT,	

	@Trigger BIT OUT

AS
BEGIN

	DECLARE @PLID INT,  @Request_Mask INT, @LRID INT, @Trans_Error INT, @TowerDesc VARCHAR(4), @Product VARCHAR(30)

	SET @Trans_Error = 0
	SET @PLID = POWER(2,30)
	SET @Request_Mask = POWER(2,1)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_01
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_01 = RSID, @SKU_01 = SKU, @PT_01 = PTID, @TID_01 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 1) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing 
					WITH (NOLOCK) 
					WHERE (CID = 1) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_01 AND CID = 1 AND TID = @TID_01

		SET @Desc_01 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_01 > 100) SET @PT_01 = (@PT_01 - 100)
	END

	IF @RSID_01 = 0 OR @RSID_01 = NULL
	BEGIN
		SET @RSID_01 = -1
		SET @SKU_01 = -1
		SET @PT_01 = -1
		SET @TID_01 = -1
	END
    	
	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 1' )

	SET @Request_Mask = POWER(2,2)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_02
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_02 = RSID, @SKU_02 = SKU, @PT_02 = PTID, @TID_02 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 2) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 2) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_02 AND CID = 2 AND TID = @TID_02

		SET @Desc_02 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_02 > 100) SET @PT_02 = (@PT_02 - 100)
	END

	IF @RSID_02 = 0 OR @RSID_02 = NULL
	BEGIN
		SET @RSID_02 = -1
		SET @SKU_02 = -1
		SET @PT_02 = -1
		SET @TID_02 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 2' )

	SET @Request_Mask = POWER(2,3)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_03
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_03 = RSID, @SKU_03 = SKU, @PT_03 = PTID, @TID_03 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 3) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 3) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_03 AND CID = 3 AND TID = @TID_03

		SET @Desc_03 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_03 > 100) SET @PT_03 = (@PT_03 - 100)
	END

	IF @RSID_03 = 0 OR @RSID_03 = NULL
	BEGIN
		SET @RSID_03 = -1
		SET @SKU_03 = -1
		SET @PT_03 = -1
		SET @TID_03 = -1
	END
	
	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 3' )

	SET @Request_Mask = POWER(2,4)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_04
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_04 = RSID, @SKU_04 = SKU, @PT_04 = PTID, @TID_04 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 4) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 4) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_04 AND CID = 4 AND TID = @TID_04

		SET @Desc_04 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_04 > 100) SET @PT_04 = (@PT_04 - 100)
	END
	
	IF @RSID_04 = 0 OR @RSID_04 = NULL
	BEGIN
		SET @RSID_04 = -1
		SET @SKU_04 = -1
		SET @PT_04 = -1
		SET @TID_04 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 4' )

	SET @Request_Mask = POWER(2,5)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_05
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_05 = RSID, @SKU_05 = SKU, @PT_05 = PTID, @TID_05 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 5) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 5) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_05 AND CID = 5 AND TID = @TID_05

		SET @Desc_05 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_05 > 100) SET @PT_05 = (@PT_05 - 100)
	END

	IF @RSID_05 = 0 OR @RSID_05 = NULL
	BEGIN
		SET @RSID_05 = -1
		SET @SKU_05 = -1
		SET @PT_05 = -1
		SET @TID_05 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 5' )

	SET @Request_Mask = POWER(2,6)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_06
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_06 = RSID, @SKU_06 = SKU, @PT_06 = PTID, @TID_06 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 6) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 6) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_06 AND CID = 6 AND TID = @TID_06

		SET @Desc_06 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_06 > 100) SET @PT_06 = (@PT_06 - 100)
	END

	IF @RSID_06 = 0 OR @RSID_06 = NULL
	BEGIN
		SET @RSID_06 = -1
		SET @SKU_06 = -1
		SET @PT_06 = -1
		SET @TID_06 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 6' )

	SET @Request_Mask = POWER(2,7)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_07
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_07 = RSID, @SKU_07 = SKU, @PT_07 = PTID, @TID_07 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 7) AND				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 7) AND 
					(PLID & @PLID = 0)))
		
		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_07 AND CID = 7 AND TID = @TID_07

		SET @Desc_07 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_07 > 100) SET @PT_07 = (@PT_07 - 100)
	END

	IF @RSID_07 = 0 OR @RSID_07 = NULL
	BEGIN
		SET @RSID_07 = -1
		SET @SKU_07 = -1
		SET @PT_07 = -1
		SET @TID_07 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 7' )

	SET @Request_Mask = POWER(2,8)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_08
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_08 = RSID, @SKU_08 = SKU, @PT_08 = PTID, @TID_08 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 8) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 8) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_08 AND CID = 8 AND TID = @TID_08

		SET @Desc_08 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_08 > 100) SET @PT_08 = (@PT_08 - 100)
	END

	IF @RSID_08 = 0 OR @RSID_08 = NULL
	BEGIN
		SET @RSID_08 = -1
		SET @SKU_08 = -1
		SET @PT_08 = -1
		SET @TID_08 = -1
	END
	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 8' )

	SET @Request_Mask = POWER(2,9)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_09
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_09 = RSID, @SKU_09 = SKU, @PT_09 = PTID, @TID_09 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 9) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 9) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_09 AND CID = 9 AND TID = @TID_09

		SET @Desc_09 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_09 > 100) SET @PT_09 = (@PT_09 - 100)
	END

	IF @RSID_09 = 0 OR @RSID_09 = NULL
	BEGIN
		SET @RSID_09 = -1
		SET @SKU_09 = -1
		SET @PT_09 = -1
		SET @TID_09 = -1
	END

	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 9' )

	SET @Request_Mask = POWER(2,10)

	UPDATE tblReleaseSequencing SET PLID = (PLID | @PLID) WHERE RSID =  @LRID_10
		
	-- Check to see if the respective cell is requesting data
	IF @Request & @Request_Mask = @Request_Mask AND @@ERROR = 0
	BEGIN 
		SELECT 	@RSID_10 = RSID, @SKU_10 = SKU, @PT_10 = PTID, @TID_10 = TID
		FROM 		tblReleaseSequencing
		WITH 		(NOLOCK) 
		WHERE 	(CID = 10) AND
				(PLID & @PLID = 0) AND 
				(Released = 0) AND 
				(RSID = (SELECT MIN(RSID) 
					FROM tblReleaseSequencing
					WITH (NOLOCK)  
					WHERE (CID = 10) AND 
					(PLID & @PLID = 0)))

		SELECT 	@TowerDesc = a.TowerDesc, @Product = b.Description 
		FROM		tblLocation a
		INNER JOIN 	tblProductList b ON a.SKU = b.SKU
		WHERE	a.SKU = @SKU_10 AND CID = 10 AND TID = @TID_10

		SET @Desc_10 = @TowerDesc + ' - ' + @Product

		-- Package Type correction for wide pallet designation, needed for package type 1
		IF (@PT_10 > 100) SET @PT_10 = (@PT_10 - 100)
	END
	
	IF @RSID_10 = 0 OR @RSID_10 = NULL
	BEGIN
		SET @RSID_10 = -1
		SET @SKU_10 = -1
		SET @PT_10 = -1
		SET @TID_10 = -1
	END
	
	IF @@ERROR <> 0 INSERT INTO tblSysEvents VALUES ( GETDATE(),@@ERROR, 'vp_ReleaseByCell - Cell 10' )

	-- Reset the correct trigger
	SET @Trigger = 0
END
GO

CREATE TABLE [dbo].[tblReleaseSequencing] (
	[RSID] [int] NULL ,
	[RouteNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RID] [int] NULL ,
	[PSID] [int] NULL ,
	[TID] [int] NULL ,
	[CID] [int] NULL ,
	[PTID] [int] NULL ,
	[SKU] [int] NULL ,
	[DID] [int] NULL ,
	[PLID] [int] NULL ,
	[Released] [bit] NULL ,
	[SCID] [int] NULL ,
	[MDID] [int] NULL ,
	[FLID] [int] NULL ,
	[TEST] [bit] NULL ,
	[WASPRINTED] [bit] NULL 
) ON [PRIMARY]
GO
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-06 : 08:10:39
you're right about being repetitive,simple? no.

can you just:
1. call the query n-times passing rsid1, rlid1 etc, instead of one time execution?
2. or make it set based, save the parameters in a table so you can compare and update by set

i'm not sure how others would approach this though...
Go to Top of Page

Kenny Blankenship
Starting Member

25 Posts
Posted - 2004-10-06 : 08:24:18
I originally created a udf and queried it multiple times. Because of the resource locking issue, I started to breakdown my original query to elimiate potential issues. Through trial and error, I found that the sproc executed faster when it did not have to make a udf call. It also reduced the number resource locking events. I would like to go back to the udf, but in the end I will go with whatever works, pretty or not.
Go to Top of Page

Kenny Blankenship
Starting Member

25 Posts
Posted - 2004-10-06 : 08:25:53
If only SQL supported arrays. . .
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-07 : 04:24:51
they might in future versions...

we can only hope
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-07 : 05:26:52
tables are arrays, no?

Go with the flow & have fun! Else fight the flow
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site