Ошибка при преобразовании типа данных varchar к bigint

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

I have a table with data and one of the columns contains a number stored as text.
When an application updates it, it writes _BAK + datetime stamp behind the number.

I’m now trying to clean up the database by deleting all records that have _BAK in the number column where the most recent one must not be deleted.

id     sitenummer                  
28376  1441_BAK20130213151952032    
28377  1441_BAK20130214142314705    

In this case the line with ID 28376 is the oldest and must be removed.

I have created a query that should do just that:

;with sel1 AS (
select t1.ID,t1.sitenummer, CONVERT(BIGint,SUBSTRING(t1.sitenummer,CHARINDEX('_',t1.sitenummer,0)+4,50)) as Stamp1    
from vdfkraan as t1
where t1.sitenummer like '%_BAK%' and (SELECT COUNT(SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) FROM vdfkraan as t2 
                                      where SUBSTRING(t1.sitenummer,0,CHARINDEX('_',t1.sitenummer,0))=SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) > 1
group by t1.id,t1.sitenummer)

, sel2 AS (
select t3.id, t3.sitenummer, t3.stamp1, 
      (select TOP(1) t4.stamp1 from sel1 as t4
        WHERE SUBSTRING(t4.sitenummer,0,CHARINDEX('_',t4.sitenummer,0)) =SUBSTRING(t3.sitenummer,0,CHARINDEX('_',t3.sitenummer,0))
        order by t3.Stamp1 DESC) AS stamp2 from sel1 as t3)

, sel3 AS (select id from sel2 where Stamp1=stamp2)

--delete FROM vdfkraan
--where id IN (SELECT t1.id FROM sel3 as t1)

--select * from sel2

If I uncomment the last line (select * from sel2), it produces the following table:

id     sitenummer                 stamp1             stamp2
28376  1441_BAK20130213151952032  20130213151952032  20130213151952032
28377  1441_BAK20130214142314705  20130214142314705  20130213151952032

Table sel3 contains one record with one column id = 28376.

So that seems to work just as I want it.

Now I comment the select line and uncomment the Delete lines.

Now I get the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

So without the delete lines, all is ok, no errors, but with it, I get this error.
I have checked the data, there should not be any problem.

What is going on here?

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

What I have tried:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

SELECT
  #dp_all.RegistrationNumber AS 'номер устройства',
  #dp_all.DeviceLocation AS 'Номер ТС',
  MAX(#card_transactions.timestamp) AS 'дата валидации',
  #dp_all.LastVersionReadTime AS 'дата связи'
FROM
  #dp_all
  INNER JOIN #card_transactions ON
    #dp_all.RegistrationNumber like '%' + #card_transactions.device_reg_no  
WHERE
  #dp_all.DeviceLocation  is not null
GROUP BY
  #dp_all.RegistrationNumber,
  #dp_all.DeviceLocation,
  #dp_all.LastVersionReadTime
ORDER BY
  #dp_all.DeviceLocation 

Где:

• RegistrationNumber -bigint,
• DeviceLocation — nvarchar,
• LastVersionReadTime — datetime,
• derice_reg_no — bigint.

Anton Shchyrov

33k2 золотых знака29 серебряных знаков59 бронзовых знаков

задан 11 сен 2018 в 13:13

7

Есть такая штука Data type precedence. Именно из-за приоритетов вы получаете ошибку при сравнении, уже неявно приобразованной строки с bigint значением.

Второй момент, сравнивать эти поля стоит тогда когда они оба будут одного типа. Преведите первый в varchar(n)/nvarchar(n) и второй с помощью CAST или CONVERT и сравнивайте вашим подходом наздоровье.

Пример условия:

ON convert(nvarchar,#dp_all.RegistrationNumber) like '%' + convert(nvarchar,#card_transactions.device_reg_no)

ответ дан 11 сен 2018 в 13:41

Nick ProskuryakovNick Proskuryakov

3,7222 золотых знака14 серебряных знаков39 бронзовых знаков

2

I have a table with data and one of the columns contains a number stored as text.
When an application updates it, it writes _BAK + datetime stamp behind the number.

I’m now trying to clean up the database by deleting all records that have _BAK in the number column where the most recent one must not be deleted.

id     sitenummer                  
28376  1441_BAK20130213151952032    
28377  1441_BAK20130214142314705    

In this case the line with ID 28376 is the oldest and must be removed.

I have created a query that should do just that:

;with sel1 AS (
select t1.ID,t1.sitenummer, CONVERT(BIGint,SUBSTRING(t1.sitenummer,CHARINDEX('_',t1.sitenummer,0)+4,50)) as Stamp1    
from vdfkraan as t1
where t1.sitenummer like '%_BAK%' and (SELECT COUNT(SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) FROM vdfkraan as t2 
                                      where SUBSTRING(t1.sitenummer,0,CHARINDEX('_',t1.sitenummer,0))=SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) > 1
group by t1.id,t1.sitenummer)

, sel2 AS (
select t3.id, t3.sitenummer, t3.stamp1, 
      (select TOP(1) t4.stamp1 from sel1 as t4
        WHERE SUBSTRING(t4.sitenummer,0,CHARINDEX('_',t4.sitenummer,0)) =SUBSTRING(t3.sitenummer,0,CHARINDEX('_',t3.sitenummer,0))
        order by t3.Stamp1 DESC) AS stamp2 from sel1 as t3)

, sel3 AS (select id from sel2 where Stamp1=stamp2)

--delete FROM vdfkraan
--where id IN (SELECT t1.id FROM sel3 as t1)

--select * from sel2

If I uncomment the last line (select * from sel2), it produces the following table:

id     sitenummer                 stamp1             stamp2
28376  1441_BAK20130213151952032  20130213151952032  20130213151952032
28377  1441_BAK20130214142314705  20130214142314705  20130213151952032

Table sel3 contains one record with one column id = 28376.

So that seems to work just as I want it.

Now I comment the select line and uncomment the Delete lines.

Now I get the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

So without the delete lines, all is ok, no errors, but with it, I get this error.
I have checked the data, there should not be any problem.

What is going on here?

• Remove From My Forums

• Question

• Hi,

  I am using one query in my Stored procedure when i am passing the values i am getting the error like Error converting data type varchar to bigint.

  select @BasicPrice = convert(float, rate_dc) from RENAULT.TMFSCBaseValue
  where PKConditionType_vc = ‘ZPRS’ and PKSalesDocType_vc = ‘YP’ and
  PKDistributionChannel_vc = ’01’ and PKDivision_vc = ’91’ 
  and  PKServiceNo_vc = @ServiceNo   and PKModelCode_vc = @ModelCode 

  and  convert(bigint,@InvoiceDate) between  convert(bigint,PKValidityStartDate_dt)

  and convert(bigint,PKValidityEndDate_dt) 

  Input values

  @DealerCode :: LN010391
  @VehSrNo :: 9ZJ16246
  @ModelCode :: LP11PB15RO11WD
  @ServiceNo :: 1
  @InvoiceDate :: 09/10/20
  @ServiceDate :: 24/06/20

  in table  TMFSCBaseValue  PKValidityStartDate_dt data type id varchar

  Please help someone  on this

  ---

  Ramana

Answers

• Change the format of @ServiceDate :: 24/06/20

  Currently, it is being passed in the format dd/mm/yy. Instead, make it as
  mm/dd/yy and you are done.

  Hope, this helps !

  ---

  -Vinay Pugalia
  If a post answers your question, please click «Mark As Answer» on that post or
  «Vote as Helpful».
  Web : Inkey Solutions

  Blog : My Blog
  Email : Vinay Pugalia

  • Marked as answer by

    Tuesday, June 28, 2011 11:35 AM

• Remove From My Forums

• Вопрос

• Hi,

  I am a newbie user of MS SQL. I’m creating this procedure and been searching for the right solution to this problem for so long. I really need help this time! When I compile the procedure, it was fine but when I try to execute it like:

  EXEC TA_COPY_TKT_DB 201166573491, 201166573491, ‘MSSQLSERVERDEV’, ‘MSSQLSERVERDEV’, ‘Demo84’, ‘Demo841’

  I get this error:

  Msg 8114, Level 16, State 5, Procedure TA_COPY_TKT_DB, Line 24

  Error converting data type varchar to bigint.

  Here’s the whole procedure I created:

  ALTER PROCEDURE [dbo].[TA_COPY_TKT_DB]

  @FromDocID
  T_DOC_ID,

  @ToDocID
  T_DOC_ID,

  @FromServerName
  VARCHAR(50),

  @ToServerName
  VARCHAR(50),

  @FromDatabaseName
  VARCHAR(50),

  @ToDatabaseName
  VARCHAR(50)

  as

  begin

    Declare

  @SqlStmt
  VARCHAR(150)

    Set Nocount On

  /* Check PS_DOC_HDR if exists */

  Begin Tran

  EXEC USP_DEL_TKT @FromDocID

  print @ToDocID

  print @FromDocID

  Set @SqlStmt = ‘INSERT INTO ‘ + @ToServerName + ‘.’ + @ToDatabaseName + ‘.dbo.PS_DOC_HDR ‘ +

    ‘SELECT * FROM ‘ + @FromServerName + ‘.’ + @FromDatabaseName + ‘.dbo.PS_DOC_HDR WHERE DOC_ID = ‘ + convert(bigint,@FromDocID)

  Commit Tran

  end

Ответы

• Your last statement is of the form

  Set @SqlStmt = <some concatened strings> + convert(bigint,@FromDocID)
  

  But the result of convert(bigint, @FromDocID) is (of course) a bigint.  So you have a string type and a bigint type.  Bigint has a higher priority then varchar, so SQL will try to convert the concatened strings to a bigint, which it can’t do so you
  get the error.

  So you must make the convert(bigint, @FromDocID) into a varchar (or char) type.  Since you declared it as a T_DOC_ID datatype and didn’t tell us what T_DOC_ID really is, it’s hard to know exactly what you want.  Some possibilities

  If T_DOC_ID is an integer type (tiny int, smallint, int, bigint) then

  Set @SqlStmt = <some concatened strings> + convert(varchar(20),@FromDocID)

  If T_DOC_ID is something else then

  Set @SqlStmt = <some concatened strings> + convert(varchar(20),convert(bigint, @FromDocID))

  Tom

  • Помечено в качестве ответа

    2 января 2012 г. 4:30

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

What I have tried:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.