1

I am writing code that needs to recognize if a column is empty or not in my database based on a specific ID. I am using Microsoft SQL Server Management Studio.

I've tried:

select count(*) as valid from table where id =7PB03150SW

But receive the error:

Conversion failed when converting the varchar value to data type int.

I also tried:

select cast(( select count(*) from table where id = 7PB03150SW)as VarChar(120))

But receive the error:

Conversion failed when converting the varchar value to data type int.

I want the code to output a single result, the number of times the specific ID appears in the table. The output needs to be able to be read by a c# file.

Diado
  • 2,229
  • 3
  • 18
  • 21
Julia
  • 45
  • 1
  • 5
  • 2
    can you show us the schema? it sounds like `id` is an int column – ps2goat Aug 13 '19 at 14:57
  • 10
    Is your `id` column an `INT`? If so, `7PB03150SW` isn't a valid value for a comparison as it's a string. If the column is actually a `VARCHAR`, you need to wrap `7PB03150SW` in quotes to denote that it's a string: `where id = '7PB03150SW'` – Diado Aug 13 '19 at 14:59
  • https://stackoverflow.com/questions/21984110/conversion-failed-when-converting-the-varchar-value-simple-to-data-type-int I think this is what you need. – acuz3r Aug 13 '19 at 14:59
  • 2
    _count(*)_ counts the rows that meet the criteria you specified. Id is a string. You left off the ' ' characters, so SQL will try to convert 7PB03150SW to int. That is causing the error – Daniel Schmid Aug 13 '19 at 15:01
  • Thank you! I was missing the ' ' – Julia Aug 13 '19 at 15:03

2 Answers2

0

The query is almost correct. You just need to add ' before and after your id value to pass it as a varchar.

So the query should be : select count(*) as valid from table where id ='7PB03150SW'

Hope it helps.

Hugo Salaun
  • 206
  • 1
  • 6
0

Try this:

Declare @ID int = 7PB03150SW Declare @SQL VARCHAR(MAX) Set @SQL = 'select count(*) from table where id='+cast(@ID AS VARCHAR) exec(@SQL)

Hope it works for you.

Farhana
  • 11
  • 2
  • Welcome to stackoverflow. When answering questions you should add an explaination as to why your code snippet will solve the OP's question. For more info check here: [answer] – Djensen Aug 15 '19 at 07:42