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'

Answer 1

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';

Answer 2

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