I have a problem with a Stored Procedure(SP) that I wrote for SQL Server 2000. It's slightly involved, but the idea is pretty simple. The SP takes 2 parameters, a BIGINT (which represents a version #) and a VARCHAR. The VARCHAR is an array represented in string format using CHAR(240) as the delimiter. There are three tables involved (tblTSDBSystem, tblMenuStructure, tblMenuGroups). First the SP reads and locks the first and only record found in the tblTSDBSystem. If the version # found in this record is less then the version number supplied to the SP, then the SP continues running, otherwise it immediately rollsback the transaction thereby releasing the record lock. As for the rest of the SP, it boils down to the following...

The SP erases all records in the tblMenuStructure table and rebuilds it using the information parsed out from the VARCHAR delimited string. Each item in the VARCHAR corresponds to a single record in the tblMenuStructure table. Once that completes, there is a quick comparision between tblMenuStructure and tblMenuGroups, and the difference (Non-Matching Records) from tblMenuGroups is stored into a temporary table. I use the results stored in the temporary table in a delete command to remove exactly those records from the tblMenuGroups table. I dispose the temporary table, and then commit the transaction in order to release the record lock on the first table tblTSDBSystem.

Now, when I run the SP, the process locks and never terminates. Not sure what I did wrong, but I am hoping someone can see something that I am apparently overlooking. I am in a real bind here and appreciate any assistance that can be offered. Below is my SP code:

-----------------------------------------------------------------------------------------
--  Procedure Name: spUpdateMenuStructure()
--  Written By:     Ben Santiago
--  Created On:     06/07/2006
--  Description:
-- 	This procedure will update the menu structure table (tblMenuStructure) based on 
--	   the text-based menu tree supplied.
--
--  Usage:
--	   spUpdateMenuStructure <ClientVersion[BigInt]>, <MenuStructure[String]>
-----------------------------------------------------------------------------------------
CREATE PROCEDURE spUpdateMenuStructure 
	@intClientVersion	BIGINT, 
	@strMenu		VARCHAR(8000) 
AS

BEGIN TRANSACTION
	-----------------------------------------
	-- Initialize Variables
	-----------------------------------------
	DECLARE @intCurrentVersion INT
	DECLARE @intCounter	INT
	DECLARE @intTotalItems	INT
	DECLARE @strItem 		VARCHAR(8000)

	-----------------------------------------
	-- Default Values
	-----------------------------------------
	SELECT @intCurrentVersion = [MenuVersion] FROM [tblTSDBSystem] WITH (SERIALIZABLE)
	IF @intCurrentVersion IS NULL
		SET @intCurrentVersion = 0
	SET @intCounter = 1

	-----------------------------------------
	-- If Client Version Is Newer...
	-----------------------------------------
	IF @intClientVersion > @intCurrentVersion
		BEGIN
			-----------------------------------------
			-- Erase tblMenuStructure Data
			-----------------------------------------
			DELETE tblMenuStructure

			-----------------------------------------
			-- Count Number of Delimiters In String
			-----------------------------------------
			SET @intTotalItems = 1
			WHILE @intCounter <= LEN(@strMenu)
				BEGIN
					IF SUBSTRING(@strMenu, @intCounter, 1) = CHAR(240)
						SET @intTotalItems = @intTotalItems + 1
				END

			-----------------------------------------
			-- Parse Menu Structure String
			-----------------------------------------
			WHILE @intCounter <= @intTotalItems
				BEGIN
					-----------------------------------------
					-- Extract Current Array Item
					-----------------------------------------
					SET @strItem = dbo.GetItemFromString(@strMenu, @intCounter, CHAR(240))

					-----------------------------------------
					-- Add Item To tblMenuStructure
					-----------------------------------------
					INSERT tblMenuStructure VALUES (@strItem)

					-----------------------------------------
					-- Increment Array Item Counter
					-----------------------------------------
					SET @intCounter = @intCounter + 1
				END

			-----------------------------------------
			-- Create Temporary Table: tblRemovedItems
			-----------------------------------------
			CREATE TABLE #tblRemovedItems (
				[Group]	CHAR(3), 
				MenuText	VARCHAR(1000), 
				Visible	BIT)

			-----------------------------------------
			-- Select List of "Removed" Menu Items From tblMenuGroups
			-----------------------------------------
			INSERT INTO #tblRemovedItems 
				SELECT [Group], MenuText, Visible 
					FROM (SELECT tblMenuGroups.[Group], 
						    tblMenuGroups.MenuText, 
						    tblMenuGroups.Visible, 
						    tblMenuStructure.MenuText AS MenuTextFilter 
						FROM 
						    tblMenuGroups LEFT OUTER JOIN tblMenuStructure ON tblMenuGroups.MenuText = tblMenuStructure.MenuText
					     ) UnMatched
					WHERE MenuTextFilter IS NULL

			-----------------------------------------
			-- Delete "Removed" Items
			-----------------------------------------
			IF @@ROWCOUNT > 0
				BEGIN
					DELETE FROM tblMenuGroups 
						WHERE MenuText IN (SELECT MenuText FROM #tblRemovedItems)
				END

			-----------------------------------------
			-- Update MenuVersion Value
			-----------------------------------------
			UPDATE [tblTSDBSystem] SET [MenuVersion] = @intClientVersion

			-----------------------------------------
			-- Remove Temporary Table(s)
			-----------------------------------------
			DROP TABLE #tblRemovedItems
		END
	ELSE
		-----------------------------------------
		-- Rollback The Transaction
		-----------------------------------------
		BEGIN
			ROLLBACK
		END

	-----------------------------------------
	-- If The Transaction Is Still Open, Commit It
	-----------------------------------------
	IF @@TRANCOUNT > 0
		COMMIT