patrickdrd
Programmer
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:
and here is the ddl for the table:
The problem is that I cannot compile this code!
Does anyone know why?
Thanks in advance!
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:
Code:
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
GOand here is the ddl for the table:
Code:
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]
GOThe problem is that I cannot compile this code!
Does anyone know why?
Thanks in advance!