Assignment to variable using a case statement over cursor results

Question

I'm trying to write a quick utility to help me generate stuff to write other SQL - particularly variable declarations. The output does not have to pretty or perfect (yet), but it has to be close enough that I can quickly and painlessly fix it. (Yes, I know it's not handling every case, yet. This is a simple case to illustrate the problem.)

use myDB

DECLARE @varcharflds table
(tname  varchar(50) not null, 
 fname  varchar(50) not null,
 dtype  varchar(50) not null,
 dlen   varchar(50) 
)

INSERT INTO @varcharflds
  SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
     FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='myTable'

select * from @varcharflds
declare utable_cursor cursor FOR
select tname, fname, dtype, dlen from @varcharflds
open utable_cursor
Declare @tname varchar(50),@fname varchar(50), @dtype varchar(50), @dlen varchar(50)
Declare @vardefstring4 varchar(3000)
Set @vardefstring4 = ''        

Fetch Next from utable_cursor into @tname, @fname, @dtype, @dlen
WHILE @@FETCH_STATUS = 0
BEGIN
  print @dlen
  set @vardefstring4 = @vardefstring4 +
     (case WHEN @dtype in ('datetime', 'date', 'bit')
      THEN @dtype ELSE  '(' + cast (@dlen as varchar(5)) + ')' END) + ', '
   Fetch Next from utable_cursor into @tname, @fname, @dtype, @dlen
END

print '>' + @vardefstring4 + '<'
print 'done'

The problem is the part that reads cast (@dlen as varchar(5)) . If I replace that with a string constant it works, but any kind of expression other than a constant does not work. It prints the 'done' message, but prints nothing for the first one - not even the angle brackets. I tried putting a try catch around it and printing an error message - no message. I thought maybe it was using some type I wasn't aware of with a null dlen ... nope. I've been working this for an embarrassing amount of time. Any help appreciated.

Answer 1

I don't have any idea why my original solution does not work, BUT I did find a different way to do it by replacing the set as follows:

set @vardefstring4 = @vardefstring4 + (case when @dlen is null THEN @dtype ELSE @dtype + '(' + cast (@dlen as varchar(5)) + ')' END) + ', '

And this has the benefit that it actually works (and I like the logic better).