Problem with procedure! Cannot compile!

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)

Problem with procedure! Cannot compile!

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2006-07-14 : 11:05:59

Hi guys,
I am writing a procedure to update a table,
passing an array from asp.net of id's,
which could be delimited with comma (",") or "-" (for a range of id's),

the code follows:


CREATE PROCEDURE sp_upd_cards (
  @array                VARCHAR(8000) = NULL
 ,@assignedtocust       BIT           = NULL
 ,@transid              INT           = NULL
 ,@s03_recuid           VARCHAR(20)   = NULL
)
AS
DECLARE @source         VARCHAR(32)
DECLARE @err            INT
DECLARE @errmsg         VARCHAR(255)
DECLARE @sentence       VARCHAR(255)
DECLARE @ErrCounter     INT

DECLARE @pos_separator  INT
DECLARE @item           INT
DECLARE @array2         VARCHAR(8000)
DECLARE @card_from      INT
DECLARE @card_to        INT
DECLARE @card_pos       INT
DECLARE @block          INT

  SET NOCOUNT ON

  SET @source = 'sp_upd_cards'
  SET @ErrCounter = 0
  SET @sentence = NULL
  SET @errmsg = NULL
  SET @card_from = NULL

  WHILE @array IS NOT NULL BEGIN
    -- Find the first delimiter
    SET @pos_separator = CHARINDEX(',', @array)
    IF @pos_separator > 0 BEGIN
      -- If found, parse out the first value…
      SET @item = CAST(LEFT(@array, @pos_separator -1) AS INT)
      -- ..and remove it from the array
      SET @array = RIGHT(@array, LEN(@array) - @pos_separator)
    END -- CHARINDEX(',', @array) > 0
    ELSE IF @pos_separator = 0 BEGIN
      -- At the end of the string, so get last value...
      SET @item = CAST(@array AS INT)
      -- ...and set @array to an empty string.
      SET @array = NULL
    END -- CHARINDEX(',', @array) = 0

    -- Do what you want with the value here

    SET @array2 = @item
    WHILE @array2 IS NOT NULL BEGIN
      SET @pos_separator = CHARINDEX('-', @array2)
      IF @pos_separator > 0 BEGIN
        -- If found, parse out the first value…
        SET @card_from = CAST(LEFT(@array2, @pos_separator -1) AS INT)
        -- ..and remove it from the array
        SET @array2 = RIGHT(@array2, LEN(@array2) - @pos_separator)
      END -- CHARINDEX('-', @array2) > 0
      ELSE IF @pos_separator = 0 BEGIN
        -- At the end of the string, so get last value...
        SET @card_to = CAST(@array2 AS INT)
        -- ...and set @array to an empty string.
        SET @array2 = NULL
      END -- CHARINDEX('-', @array2) = 0
    END -- WHILE @array2 IS NOT NULL

    IF @card_from IS NOT NULL
      SET @card_pos = @card_from
      WHILE @card_pos < @card_to
LockTimeOutRetry1:
        SET @block = 1
        IF ( NOT EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos) ) BEGIN
          SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it was not found.' + CHAR(10)
        END
        ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos AND assignedtocust = 1 AND transid IS NOT NULL)) AND (@transid IS NOT NULL) ) BEGIN
          SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it is already assigned to another customer''s transaction.' + CHAR(10)
        END
        ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos AND assignedtocust = 0 AND transid IS NULL))     AND (@transid IS NULL) ) BEGIN
          SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it is not assigned to a customer''s transaction.' + CHAR(10)
        END
        ELSE BEGIN
          SET @sentence = 'Error updating card [' + CAST(@card_pos AS VARCHAR) + '].'
          UPDATE CARDS
          SET    assignedtocust = @assignedtocust
                ,transid        = @transid
          WHERE  cardid         = @card_pos
          SET @err = @@ERROR
          IF @err > 0 GOTO ErrorHandler
          SET @sentence = 'Card updated successfully.'
        END

        SET @card_pos = @card_pos + 1
      END -- WHILE @card_pos < @card_to
    END -- @card_from IS NOT NULL
    ELSE IF @card_from IS NULL BEGIN
LockTimeOutRetry2:
      SET @block = 2
      IF ( NOT EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item) ) BEGIN
        SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it was not found.' + CHAR(10)
      END
      ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item AND assignedtocust = 1 AND transid IS NOT NULL)) AND (@transid IS NOT NULL) ) BEGIN
        SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it is already assigned to another customer''s transaction.' + CHAR(10)
      END
      ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item AND assignedtocust = 0 AND transid IS NULL))     AND (@transid IS NULL) ) BEGIN
        SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it is not assigned to a customer''s transaction.' + CHAR(10)
      END
      ELSE BEGIN
        SET @sentence = 'Error updating card [' + CAST(@item AS VARCHAR) + '].'
        UPDATE CARDS
        SET    assignedtocust = @assignedtocust
              ,transid        = @transid
        WHERE  cardid         = @item
        SET @err = @@ERROR
        IF @err > 0 GOTO ErrorHandler
        SET @sentence = 'Card updated successfully.'
      END
    END -- @card_from IS NULL
  END -- WHILE @array IS NOT NULL

  IF @errmsg IS NOT NULL BEGIN
    SET @errmsg = SUBSTRING(@errmsg, 1, LEN(@errmsg) - 1)
    RAISERROR (@errmsg, 16, 1)
    RETURN -100
  END
  ELSE BEGIN
    RETURN 0
  END

ErrorHandler:
  IF (@err = 1222 OR @err = 1205) AND @ErrCounter = 5
    BEGIN
      RAISERROR ('Unable to Lock Data after five attempts.', 16,1)
      RETURN -100
    END
  IF @err = 1222 OR @err = 1205 -- Lock Timeout / Deadlock
    BEGIN
      WAITFOR DELAY '00:00:00.25'
      SET @ErrCounter = @ErrCounter + 1
      IF @block = 1 BEGIN
        GOTO LockTimeOutRetry1
      END
      ELSE IF @block = 2 BEGIN
        GOTO LockTimeOutRetry2
      END
    END
  -- else unknown error
  SELECT @errmsg = description
  FROM   MASTER.DBO.SYSMESSAGES
  WHERE  error = @err
  EXEC   spLogError @source, @err, @errmsg, @sentence

  RAISERROR (@err, 16, 1)
--  RAISERROR (@err, 16, 1) WITH LOG
  RETURN -100
GO

and here is the ddl for the table:


CREATE TABLE [Cards] (
	[CardID] [int] NOT NULL ,
	[TAC_ID] [varchar] (50) NOT NULL ,
	[AssignedToCust] [int] NOT NULL ,
	[TransID] [int] NULL ,
	[DateOpened] [smalldatetime] NULL ,
	[isonlinesale] [bit] NOT NULL CONSTRAINT [DF_Cards_isonlinesale] DEFAULT (0),
	[s03_recuid] [varchar] (20) NULL ,
	[s03_recdate] [smalldatetime] NULL ,
	[s03_recdeleted] [int] NULL CONSTRAINT [DF__Cards__s03_recde__3FF073BA] DEFAULT (0),
	[s03_recflag] [int] NULL CONSTRAINT [DF__Cards__s03_recfl__40E497F3] DEFAULT (0),
	[rowversion] [timestamp] NULL ,
	CONSTRAINT [PK_Cards] PRIMARY KEY  NONCLUSTERED 
	(
		[CardID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

The problem is that I cannot compile this code!
Does anyone know why?

Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-14 : 11:37:22

There are lots of Begin...End mismatches. Check the following version:

CREATE PROCEDURE sp_upd_cards (
@array                VARCHAR(8000) = NULL
,@assignedtocust       BIT           = NULL
,@transid              INT           = NULL
,@s03_recuid           VARCHAR(20)   = NULL
)
AS
DECLARE @source         VARCHAR(32)
DECLARE @err            INT
DECLARE @errmsg         VARCHAR(255)
DECLARE @sentence       VARCHAR(255)
DECLARE @ErrCounter     INT

DECLARE @pos_separator  INT
DECLARE @item           INT
DECLARE @array2         VARCHAR(8000)
DECLARE @card_from      INT
DECLARE @card_to        INT
DECLARE @card_pos       INT
DECLARE @block          INT

SET NOCOUNT ON

SET @source = 'sp_upd_cards'
SET @ErrCounter = 0
SET @sentence = NULL
SET @errmsg = NULL
SET @card_from = NULL

WHILE @array IS NOT NULL
BEGIN
	-- Find the first delimiter
	SET @pos_separator = CHARINDEX(',', @array)
	IF @pos_separator > 0 
	BEGIN
		-- If found, parse out the first value…
		SET @item = CAST(LEFT(@array, @pos_separator -1) AS INT)
		-- ..and remove it from the array
		SET @array = RIGHT(@array, LEN(@array) - @pos_separator)
	END -- CHARINDEX(',', @array) > 0
	ELSE IF @pos_separator = 0 
	BEGIN
		-- At the end of the string, so get last value...
		SET @item = CAST(@array AS INT)
		-- ...and set @array to an empty string.
		SET @array = NULL
	END -- CHARINDEX(',', @array) = 0

	-- Do what you want with the value here

	SET @array2 = @item

	WHILE @array2 IS NOT NULL 
	BEGIN
		SET @pos_separator = CHARINDEX('-', @array2)
		IF @pos_separator > 0 
		BEGIN
			-- If found, parse out the first value…
			SET @card_from = CAST(LEFT(@array2, @pos_separator -1) AS INT)
			-- ..and remove it from the array
			SET @array2 = RIGHT(@array2, LEN(@array2) - @pos_separator)
		END -- CHARINDEX('-', @array2) > 0
		ELSE IF @pos_separator = 0 
		BEGIN
			-- At the end of the string, so get last value...
			SET @card_to = CAST(@array2 AS INT)
			-- ...and set @array to an empty string.
			SET @array2 = NULL
		END -- CHARINDEX('-', @array2) = 0
	END -- WHILE @array2 IS NOT NULL

	IF @card_from IS NOT NULL
	BEGIN
		SET @card_pos = @card_from

		WHILE @card_pos < @card_to
		begin
			LockTimeOutRetry1:
			SET @block = 1
			IF ( NOT EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos) ) 
			BEGIN
				SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it was not found.' + CHAR(10)
			END
			ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos AND assignedtocust = 1 AND transid IS NOT NULL)) AND (@transid IS NOT NULL) ) 
			BEGIN
				SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it is already assigned to another customer''s transaction.' + CHAR(10)
			END
			ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @card_pos AND assignedtocust = 0 AND transid IS NULL))     AND (@transid IS NULL) ) 
			BEGIN
				SET @errmsg = '[' + CAST(@card_pos AS VARCHAR) + '] was not updated because it is not assigned to a customer''s transaction.' + CHAR(10)
			END
			ELSE 
			BEGIN
				SET @sentence = 'Error updating card [' + CAST(@card_pos AS VARCHAR) + '].'
				UPDATE CARDS
				SET    assignedtocust = @assignedtocust
				,transid        = @transid
				WHERE  cardid         = @card_pos
				SET @err = @@ERROR
				
				IF @err > 0 GOTO ErrorHandler
					SET @sentence = 'Card updated successfully.'
			END
		
			SET @card_pos = @card_pos + 1
		END -- WHILE @card_pos < @card_to
	END -- @card_from IS NOT NULL
	ELSE IF @card_from IS NULL 
	BEGIN
		LockTimeOutRetry2:
		SET @block = 2
		IF ( NOT EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item) ) 
		BEGIN
			SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it was not found.' + CHAR(10)
		END
		ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item AND assignedtocust = 1 AND transid IS NOT NULL)) AND (@transid IS NOT NULL) ) 
		BEGIN
			SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it is already assigned to another customer''s transaction.' + CHAR(10)
		END
		ELSE IF ( (EXISTS (SELECT 1 FROM CARDS WHERE cardid = @item AND assignedtocust = 0 AND transid IS NULL))     AND (@transid IS NULL) ) 
		BEGIN
			SET @errmsg = '[' + CAST(@item AS VARCHAR) + '] was not updated because it is not assigned to a customer''s transaction.' + CHAR(10)
		END
		ELSE 
		BEGIN
			SET @sentence = 'Error updating card [' + CAST(@item AS VARCHAR) + '].'
			UPDATE CARDS
			SET    assignedtocust = @assignedtocust
			,transid        = @transid
			WHERE  cardid         = @item
			SET @err = @@ERROR

			IF @err > 0 GOTO ErrorHandler
				SET @sentence = 'Card updated successfully.'
		END
	END -- @card_from IS NULL
END -- WHILE @array IS NOT NULL

IF @errmsg IS NOT NULL 
BEGIN
	SET @errmsg = SUBSTRING(@errmsg, 1, LEN(@errmsg) - 1)
	RAISERROR (@errmsg, 16, 1)
	RETURN -100
END
ELSE 
BEGIN
	RETURN 0
END

ErrorHandler:
IF (@err = 1222 OR @err = 1205) AND @ErrCounter = 5
BEGIN
	RAISERROR ('Unable to Lock Data after five attempts.', 16,1)
	RETURN -100
END
IF @err = 1222 OR @err = 1205 -- Lock Timeout / Deadlock
BEGIN
	WAITFOR DELAY '00:00:00.25'
	SET @ErrCounter = @ErrCounter + 1
	IF @block = 1 
	BEGIN
		GOTO LockTimeOutRetry1
	END
	ELSE IF @block = 2 
	BEGIN
		GOTO LockTimeOutRetry2
	END
END
-- else unknown error
SELECT @errmsg = description
FROM   MASTER.DBO.SYSMESSAGES
WHERE  error = @err
EXEC   spLogError @source, @err, @errmsg, @sentence

RAISERROR (@err, 16, 1)
--  RAISERROR (@err, 16, 1) WITH LOG
RETURN -100
GO

Harsh Athalye
India.
"Nothing is Impossible"

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-07-14 : 16:46:39

You also have a logic problem. You are giving @Item a value based on the location of ','. But @Item is an int... if there is a '-' in that string this will get a conversion error.

Then you try to make @Array2 = @Item to look for the '-' but the only time this will give a valid answer is when the above error didn't happen, which means there is no '-' in it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources