1

Here is my Query:

 DECLARE @RecordCount as INT
 DECLARE @today as VARCHAR(10)

 SET @today = convert(varchar(10),getdate(),120)
  Set @RecordCount = (Select COUNT(*)
 FROM tableABC

 select case when @RecordCount = 0
  THEN 'There was no data found for tableABC: ' + @Today 
  ELSE 'tableABC imported ' + @RecordCount +' rows for date range ' + @Today
  END

Error Message

Msg 245, Level 16, State 1, Line 8 Conversion failed when converting the varchar value 'tableABC imported' to data type int.

Why do I get this error???

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
RickyTW
  • 23
  • 3
  • 1
    The error message contains everything what you need to solve the problem. – Pred Sep 28 '15 at 13:26
  • @recordCount is an int - you'll need to cast it – Clay Sep 28 '15 at 13:27
  • You are trying to concatenate a `VARCHAR` string ('tableABC imported') with an integer (`Select COUNT(*) from table ABC`) – Sam Cohen-Devries Sep 28 '15 at 13:27
  • possible duplicate of [SQL Server 2012: How to display the @Var result inside a Case statement with literals before and after the @Var](http://stackoverflow.com/questions/32813024/sql-server-2012-how-to-display-the-var-result-inside-a-case-statement-with-lit) – Kritner Sep 28 '15 at 16:30

2 Answers2

1

SQL Server try to convert string to int because of Data Type Precedence:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

INT is 16 on list of precedece and string constant(VARCHAR) is lower. So SQL Server try casting every string to INT.

Cast RecordCount to string:

DEMO

select 
   case when @RecordCount = 0
     THEN 'There was no data found for tableABC: ' + @Today 
     ELSE 'tableABC imported ' + CAST(@RecordCount AS NVARCHAR(100)) +' rows for date range ' + @Today
   end

also you should consider using CONCAT for string concatenation instead of +. Then you don't need to cast:

select 
   case when @RecordCount = 0
     THEN CONCAT('There was no data found for tableABC: ',@Today)
     ELSE CONCAT('tableABC imported ', @RecordCount,' rows for date range ', @Today)
   end

Another solution is to use FORMATMESSAGE:

DECLARE @RecordCount as INT = 1;
DECLARE @today as VARCHAR(10) = convert(varchar(10),getdate(),120);

SELECT
  CASE 
   WHEN @RecordCount = 0 THEN FORMATMESSAGE('There was no data found for tableABC: %i', @RecordCount)
   ELSE FORMATMESSAGE('tableABC imported %i rows for date range %s', @RecordCount, @today)
  END
Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1
select case when @RecordCount = 0
  THEN 'There was no data found for tableABC: ' + @Today 
  ELSE 'tableABC imported ' + cast(@RecordCount as varchar(10)) +' rows for date range ' + @Today
  END

The problem is that when you combine things in a string, all of the elements of that string need to be a text based not numeric datatype. Since you use @RecordCount as an integer earlier, the best best is to cast it to varchar in the else statement. There is implicit conversion between varchar and int but SQL server, for reasons that surpass my understanding, has chosen to always try to convert the text to an int rather than the other way around. Therefore the tex can;t convert and so you get the error message.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • `" There is implicit conversion between varchar and int but SQL server, for reasons that surpass my understanding"` there is simple solution :) [Data Type Precedence](https://msdn.microsoft.com/en-us/library/ms190309.aspx) – Lukasz Szozda Sep 28 '15 at 13:36
  • I know the precedences, what I don't understand is the reasoning behind the precendences – HLGEM Sep 28 '15 at 13:55
  • This is rather clear for most broad to most narrow ones. – Lukasz Szozda Sep 28 '15 at 13:55
  • BUt since it makes much more sense for varchar to take precedence over int, I still don't get it. ((most of time when you need this implicit conversion it needs to convert ints to varchars not the other way.) The way they did it guarantees more errors not fewer. – HLGEM Sep 28 '15 at 17:24
  • It is the same logic like `every square is rectangle, but not every rectangle is square`. SQL Server always can cast INT to varchar but not vice versa – Lukasz Szozda Sep 29 '15 at 01:47