You're incrementing wrong variable
WHILE @intCounter <= LEN(@strMenu)
BEGIN
IF SUBSTRING(@strMenu, @intCounter, 1) = CHAR(240)
SET @intTotalItems = @intTotalItems + 1
END
>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:
>
>
>
>
>
>
>
>
>
>
>
>
>
>CREATE PROCEDURE spUpdateMenuStructure
> @intClientVersion BIGINT,
> @strMenu VARCHAR(8000)
>AS
>
>BEGIN TRANSACTION
>
>
>
> DECLARE @intCurrentVersion INT
> DECLARE @intCounter INT
> DECLARE @intTotalItems INT
> DECLARE @strItem VARCHAR(8000)
>
>
>
>
> SELECT @intCurrentVersion = [MenuVersion] FROM [tblTSDBSystem] WITH (SERIALIZABLE)
> IF @intCurrentVersion IS NULL
> SET @intCurrentVersion = 0
> SET @intCounter = 1
>
>
>
>
> IF @intClientVersion > @intCurrentVersion
> BEGIN
>
>
>
> DELETE tblMenuStructure
>
>
>
>
> SET @intTotalItems = 1
> WHILE @intCounter <= LEN(@strMenu)
> BEGIN
> IF SUBSTRING(@strMenu, @intCounter, 1) = CHAR(240)
> SET @intTotalItems = @intTotalItems + 1
> END
>
>
>
>
> WHILE @intCounter <= @intTotalItems
> BEGIN
>
>
>
> SET @strItem = dbo.GetItemFromString(@strMenu, @intCounter, CHAR(240))
>
>
>
>
> INSERT tblMenuStructure VALUES (@strItem)
>
>
>
>
> SET @intCounter = @intCounter + 1
> END
>
>
>
>
> CREATE TABLE #tblRemovedItems (
> [Group] CHAR(3),
> MenuText VARCHAR(1000),
> Visible BIT)
>
>
>
>
> 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
>
>
>
>
> IF @@ROWCOUNT > 0
> BEGIN
> DELETE FROM tblMenuGroups
> WHERE MenuText IN (SELECT MenuText FROM #tblRemovedItems)
> END
>
>
>
>
> UPDATE [tblTSDBSystem] SET [MenuVersion] = @intClientVersion
>
>
>
>
> DROP TABLE #tblRemovedItems
> END
> ELSE
>
>
>
> BEGIN
> ROLLBACK
> END
>
>
>
>
> IF @@TRANCOUNT > 0
> COMMIT
>
--sb--
