question

vigneshwaran avatar image
vigneshwaran asked

Conversion failed when converting the varchar value '')' to data type int

I have a query below , in that with out count function .i got answer,if using count function ,i got this error ---->Conversion failed when converting the varchar value '')' to data type int. Please crack me out.. select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) values ( '''+'Library'+''','''+isnull('CheckOut -'+cio_branchname,'')+''', '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''', '''+ISNULL(mstmember.m_mobile,'')+''','''+count(trncheckinout.cio_bookname)+''')' AS MAILQRY from trncheckinout inner join mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208 group by m_email1,m_mobile, cio_branchname,cio_membername
sql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
The real problem is this section of your code: +count(trncheckinout.cio_bookname)+''')' Let's say that this command "count(trncheckinout.cio_bookname)" has returned you a number 100 now the next thing will look something like this: 100+''')' meaning your "+" sign which you are using as a concatenation operator will act as a mathematical function and will try to add ')' to the value 100 where it will fail behind conversion error. change the last operator from + to & and I believe it will work. Hope this help.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image @SQLShark commented ·
Or convert it to a char/varchar
3 Likes 3 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
& is not used for concatenation, that's a bitwise AND operator. The only way to handle this is to convert all elements to char/varchar
1 Like 1 ·
Sule avatar image
Sule answered
The problem is that you need to convert or cast count(something) to varchar data type if you are doing concatenation. You can't concatenate numeric data types, only textual (varchar,char, nvarchar...). e.g. '+ CAST(count(trncheckinout.cio_bookname) AS varchar(100)) +' Your query: select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) values ( '''+'Library'+''','''+isnull('CheckOut -'+cio_branchname,'')+''', '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''', '''+ISNULL(mstmember.m_mobile,'')+''','''+CAST(count(trncheckinout.cio_bookname) AS Varchar(100))+''')'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.