October 5, 2007 at 4:32 am
ALTER PROCEDURE [dbo].[sp_CheckAndUpdateErrorRectifyDate]
@NozzleID int,
@ParserString varchar(MAX)
AS
BEGIN
DECLARE @NextPos int
DECLARE @LoopCond tinyint
DECLARE @PreviousPos int
DECLARE @FlgFirst bit
DECLARE @QueryCondition varchar(MAX)
SET @LoopCond=1
SET @NextPos =0
SET @FlgFirst=0
SET @QueryCondition=''
WHILE (@LoopCond=1)
BEGIN
--Retrieving the Position of the delimiter
SET @NextPos =@NextPos + 1
SET @NextPos = CHARINDEX(',',@ParserString, @NextPos)
--Retreiving the last substring
IF(@NextPos=0)
BEGIN
PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)
SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)
SET @PreviousPos = @NextPos
BREAK
END
--Retrieving the individual substrings
If @FlgFirst=0
--Retreiving the first substring
BEGIN
SET @FlgFirst=1
PRINT SUBSTRING(@ParserString,1, @NextPos-1)
SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos-1) AS bigint)
SET @PreviousPos = @NextPos
END
ELSE
--Retreiving the internmediate substrings
BEGIN
PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos)
SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos) AS bigint)
SET @PreviousPos = @NextPos
END
END
print 'ErrorId <>' + @QueryCondition
UPDATE[ESMS2_DBMS].[dbo].[ErrorDetails]
SETErrorRectifyDateTime=GETDATE()
WHERE(NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)
END
October 5, 2007 at 6:23 am
you are trying to concatenate a string (.... ' AND ErrorId <> ') and something you convert to bigint. What is happening above, is that the first string is actually converted to bigint (and this fails), and is added, not concatenated to the bigint. Since you want concatenation, you have to make sure that the bigint is varchar.
so use:
CAST(CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint) as varchar(100))
Regards,
Andras
October 5, 2007 at 6:25 am
To demonstrate the exact problem (the solution is in my previous answer) just see what this results:
select 'this will be converted to bigint and will fail' + cast ('2' as bigint)
select 'this stays as a string and concatenation is used' + cast(cast ('2' as bigint) as varchar(100))
Regards,
Andras
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply