sql server 游标组装动态sql

Declare @sql varchar(8000)

Declare @itemid varchar(10)

Declare @itemName varchar(200)

Declare Cur Cursor For Select DICT_ITEM_ID, ITEM_NAME  From BASE_DICT_ITEM where dict_id = 'promotionMode'

set @sql = 'select ty.main_id, '

Open Cur

Fetch next From Cur Into @itemid, @itemName

While @@fetch_status=0 

Begin

    --print @itemid

    set @sql = @sql + 'MAX(CASE ty.[TYPE_ID] WHEN ' + @itemid + ' THEN '''+@itemName+','' ELSE '''' END) + ' 

    Fetch Next From Cur Into @itemid, @itemName

End   

set @sql = subString(@sql, 1, len(@sql)-2)

set @sql = @sql+ ' types from BIZ_PROM_TYPES ty, BASE_DICT_ITEM item where ty.MAIN_ID = item.DICT_ITEM_ID group by ty.main_id'

--print @sql

Close Cur   

Deallocate Cur

exec (@sql)