Manoj Bandgar


I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ',' into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.

ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]

@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]

SET ErrorRectifyDateTime=GETDATE()

WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)

END





Re: Error converting data type varchar to bigint

afung


SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)

Why would you cast the string value as bigint, then concatenate it back with a varchar data type variable This forces SQL to do implicit data type conversions.

I'm guessing the data type for the ErrorId column is bigint. SQL cannot convert @QueryCondition from varchar to bigint b/c the variable contains characters.






Re: Error converting data type varchar to bigint

fleo

Wow that's quite an algorithm...

I'm not sure I understand 100% your need but here's what we use in similar cases.

I would create a table function taking as parameter @ParserString, the comma-delimited string.

When invoking the function, you should get a row per element:

DECLARE @ParserString varchar(MAX)

SET @ParserString = 'A, B, C'

SELECT ErrorID FROM dbo.fn_ParseString(@ParserString)

/*

ErrorID

--------

A

B

C

*/

Then you could use a statement similar to this:

UPDATE dbo.ErrorDetails

SET ErrorRectifyDateTime=GETDATE()

WHERE NozzleId = @NozzleId

AND ErrorRectifyDateTime IS NULL

AND ErrorID NOT IN (SELECT ErrorID FROM dbo.fn_ParseString(@ParserString))

Hope it helps...






Re: Error converting data type varchar to bigint

DaleJ

Since you're constructing selection criteria for your update statement on the fly, you need to execute the resulting update statement via dynamic SQL.

The error is coming from the attempt to compare @QueryCondition with ErrorId.






Re: Error converting data type varchar to bigint

Louis Davidson

Can you provide the input that is giving you the issue That would definitely help.






Re: Error converting data type varchar to bigint

dyarwood

Looking at the query I would think that the @QueryCondition is not a bigint type.

Assuming ErrorId is of type bigint then as declared @QueryCondition is a varchar and can not implicitly be converted to a bigint. Also from the looks of the query the @QueryCondition would start with 'AND ErrorId <> '

If you are passing in the ErrorIds in the ParserString why not build up a sql string and use the EXEC command

eg

Code Block

DECLARE @sql varchar(MAX)

SET @sql = 'UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails] '

SET @sql = @sql + 'SET ErrorRectifyDateTime=GETDATE() WHERE (NozzleId = @NozzleId) '

SET @sql = @sql + 'AND(ErrorRectifyDateTime IS NULL) AND (ErrorId NOT IN ' + @ParserString + ')'

EXEC (@sql)

Hope this helps





Re: Error converting data type varchar to bigint

Louis Davidson

>>The error is coming from the attempt to compare @QueryCondition with ErrorId.<<

Nice catch Dale. My brain gets mushy with such big queries sometimes, until I see what they are passing in Smile






Re: Error converting data type varchar to bigint

Manoj Bandgar

Thanks friends for your replies

Finally following is the dynamic sql modified statement.

SELECT @Sql = 'UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails] '

SELECT @Sql = @Sql + ' SET ErrorRectifyDateTime = CONVERT(VARCHAR(25),GETDATE())'

SELECT @Sql = @Sql + ' WHERE (NozzleId = ' + CONVERT(VARCHAR(20),@NozzleId) + ') AND (ErrorRectifyDateTime IS NULL)'

SELECT @Sql = @Sql + ' AND (ErrorId NOT IN (SELECT ErrorId FROM [ESMS2_DBMS].[dbo].[' + @TempTable + '])) '

print @Sql

EXEC sp_executesql @Sql