June 1, 2011 at 12:28 pm
Below is a script and output. Why does it not work? I don't understand why @cmd does not have the contents of @x + @y or why I can't just do SET @cmd = @cmd + '.....'.
declare @cmd nchar(2000)
declare @tbl nchar(100)
declare @col nchar(100)
declare dbList cursor for
select table_name, column_name from INFORMATION_SCHEMA.COLUMNS where data_type = 'bit' order by table_name
declare @last_tbl char(100) = 'XX', @first bit = 0, @cnt int = 0, @x nchar(2000), @y nchar(2000)
OPEN dbList
FETCH NEXT FROM dbList
INTO @tbl, @col
WHILE @@FETCH_STATUS = 0
BEGIN
set @cnt = @cnt + 1
IF @last_tbl != @tbl
BEGIN
IF @last_tbl != 'xx'
--exec sp_executesql @cmd
print @cmd
SET @cmd = 'update ' + rtrim( @tbl) + ' set '
SET @first = 1
SET @last_tbl = @tbl
END
IF @first = 0
SET @cmd = @cmd + ', '
SET @first = 0
--SET @cmd = @cmd + 'case when ' + RTRIM( @col) + ' is null else ' + RTRIM( @col) + ' end '
set @x = @cmd
set @y = 'case when ' + RTRIM( @col) + ' is null else ' + RTRIM( @col) + ' end '
set @cmd = @x + @y
FETCH NEXT FROM dbList INTO @tbl, @col
IF @cnt > 10
begin
CLOSE dbList
DEALLOCATE dbList
RETURN
END
END
--exec sp_executesql @cmd
print @cmd
CLOSE dbList
DEALLOCATE dbList
return
Results:
update account set
update acctsrec set
Thank you for any advice.
Mike
June 1, 2011 at 1:33 pm
It's working. You just can't see it with your print statement because you aren't trimming spaces from @X.
Try this
set @cmd = rtrim(@x) + ' '+ @y
print @cmd
P.S. You still have some work to do. You are missing the name of the column you want to update with your case statement.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 1, 2011 at 1:43 pm
Thank you very much. I was pounding my head against a wall.
Mike
June 1, 2011 at 2:05 pm
No problem, Mike. We all have moments like that.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply