sql server conditionals

Question

I am getting an error when I run this query because the data type money can't be implicitly converted to a varchar. However, I am using an if statemnt to make sure the data type is not money before I try the conversion. Clearly, the conversion is being executed anyways. Anyone know why?

table: BBH_NEW col: rebate2
datatype: money

if 'money'= 'money'
begin
   if (select max([rebate2]) from [BBH_NEW]) = 0 
   and (select min([rebate2]) from [BBH_NEW]) = 0
     print ' rebate2 '
     print ' 1 '
end   

if 'money'!= 'money'
begin
   IF NOT EXISTS (SELECT top 1 * FROM [BBH_NEW] WHERE [rebate2] IS NOT NULL and
   len([rebate2]) > 0 ) 
   BEGIN 
      print ' rebate2 ' 
   end
end

Error:
Msg 257, Level 16, State 3, Line 11
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

yes this code was generated. If it helps, this is the code which was used to produce it:

select @temp = 
    data_type FROM information_schema.columns
WHERE table_schema = 'dbo'
AND table_name = @tblname 
AND column_name = @col

SELECT @hold = 
    'if '''+@temp+'''= ''money'' 
begin
   if (select max(['+@col+']) from ['+@tblname+']) = 0 
       and (select min(['+@col+']) from ['+@tblname+']) = 0
   print '' '+@col+' money''
end 

    if '''+@temp+'''!= ''money'' 
begin
   IF NOT EXISTS (SELECT max([' + @col + ']) FROM ['+ @tblname + '] 
       WHERE len( [' + @col + ']) > 0 ) 
       BEGIN 
          print '' ' + @col + ' '' 
       end 
end'

when is the literal string 'money' not going to equal itself? — dnord, Feb 26 '10 at 19:38
This code is generated dynamically. Its not usually 'money' != 'money' , it uses the current columns datatype. i.e. if 'varchar' != 'money'. I used this example because its trying to tell me that in fact 'money' != 'money' since it is executing the next line — Colin, Feb 26 '10 at 19:42
So *the provided* code was actually generated and produced the error? I run this in SSMS 2008 and it runs without flaws. — Austin Salonen, Feb 26 '10 at 20:05
I run it in SSMS 2008 and get errors. See the edit above for the source code — Colin, Feb 26 '10 at 20:15
I'm pretty sure you can use either http://stackoverflow.com/questions/7884/testing-for-inequality-in-t-sql — Colin, Feb 26 '10 at 22:53
@ajdams: Standard SQL doesn't use !=, but Microsoft's SQL Server does. — Shannon Severance, Feb 28 '10 at 04:01

Ross Bradbury · Accepted Answer · 2010-02-27T06:04:22.647

As I understand it the column BBH_NEW.rebate2 is of type money when you get the error. In T-SQL you can't have a query that doesn't compile, and that is what you are encountering. Even though the query in the always-false if block won't run, it doesn't compile because the data types don't match.

First, a quick solution for you - use CONVERT or CAST to explicitly change the data type.

if 'money'!= 'money'
begin
   IF NOT EXISTS (SELECT top 1 * FROM [BBH_NEW] WHERE [rebate2] IS NOT NULL and
   len(CONVERT(VARCHAR(8000), [rebate2])) > 0 ) 
   BEGIN 
      print ' rebate2 ' 
   end
end

But, there has to be a better way to do whatever you are doing... When does that SQL get generated? If it is at runtime, can you just not generate the part that won't run? Maybe something like this?

SELECT @hold = CASE WHEN @temp = 'money' THEN 
    'if (select max(['+@col+']) from ['+@tblname+']) = 0 
       and (select min(['+@col+']) from ['+@tblname+']) = 0
   print '' '+@col+' money'''
ELSE
    'IF NOT EXISTS (SELECT max([' + @col + ']) FROM ['+ @tblname + '] 
       WHERE len( [' + @col + ']) > 0 ) 
       BEGIN 
          print '' ' + @col + ' '' 
       end'
END

or maybe change the generation to this...

SELECT @temp = DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tblname 
AND COLUMN_NAME = @col

IF(@temp = 'money')
    SELECT @hold = 'IF(EXISTS(
    SELECT 1 
    FROM ['+@tblname+'] 
    HAVING MAX(['+@col+']) = 0 
    AND MIN(['+@col+']) = 0))
BEGIN
    PRINT '' '+@col+' ''
END';
ELSE
    SELECT @hold = 'IF(NOT EXISTS(
    SELECT * 
    FROM ['+@tblname+'] 
    WHERE ['+@col+'] IS NOT NULL
    AND LEN(['+@col+']) > 0))
BEGIN
    PRINT '' '+@col+' ''
END';

Thanks, I didn't realize the error was coming from compiling vs at runtime — Colin, Mar 01 '10 at 14:13

score 0 · Answer 2 · answered Feb 27 '10 at 07:05

0

Some hints to optimize the generator

Rewrite

SELECT @hold = 
'if '''+@temp+'''= ''money'' 
begin
...
end

as

if @temp = 'money'
begin
...
end

Second, I cannot think of a case when

[rebate2] IS NOT NULL

does not imply

len(CONVERT(VARCHAR(8000), [rebate2])) > 0

in other words, as soon as rebate2 is not NULL, its string length is greater 0

answered Feb 27 '10 at 07:05

devio

36,858
7
80
143

"in other words, as soon as rebate2 is not NULL, its string length is greater 0" No, SQL Server distiguishes between the empty string '' and null. – Shannon Severance Feb 28 '10 at 04:03
@Shannon Severance which MONEY NOT NULL value has as representation the empty string. – devio Feb 28 '10 at 10:52
you are correct. I spoke without thinking everything through. – Shannon Severance Feb 28 '10 at 21:12

sql server conditionals

2 Answers2