use pubsgodeclare @table nvarchar(100), @target_cols nvarchar(4000), @include bit -- include or exclude target columns ?select @table = 'jobs', @target_cols = '', @include = 1-- STARTdeclare @declaration nvarchar(4000), @read_record nvarchar(4000), @write_record nvarchar(4000), @print_result nvarchar(4000)select @declaration = '', @read_record = '', @write_record = '', @print_result = ''-- generate declaration list and assignment listselect @declaration = @declaration + declaration, @read_record = @read_record + read_record, @write_record = @write_record + write_record, @print_result = @print_result + print_stmtfrom(select declaration = variable + ' ' + upper(DATA_TYPE) collate chinese_prc_bin + case when charindex('char', DATA_TYPE) > 0 then + '(' + convert(varchar, CHARACTER_MAXIMUM_LENGTH) + ')' when charindex('decimal', DATA_TYPE) > 0 then '(' + convert(varchar, NUMERIC_PRECISION) + ',' + convert(varchar, NUMERIC_SCALE) + ')' else '' end + ',', read_record = variable + ' = ' + COLUMN_NAME + ',', write_record = COLUMN_NAME + ' = ' + variable + ',', print_stmt = 'print ''' + variable + ' = '' + isnull(cast(' + variable + ' as varchar), ''null'')' + char(13)from (select variable = case when charindex('text', DATA_TYPE) > 0 then null when charindex('image', DATA_TYPE) > 0 then null else '@' + case when charindex('char', DATA_TYPE) > 0 then + 'c' when charindex('int', DATA_TYPE) > 0 then + 'i' when charindex('decimal', DATA_TYPE) > 0 then 'd' when charindex('real', DATA_TYPE) > 0 then 'd' when charindex('float', DATA_TYPE) > 0 then 'f' when charindex('bit', DATA_TYPE) > 0 then + 'b' when charindex('datetime', DATA_TYPE) > 0 then + 'd' when charindex('money', DATA_TYPE) > 0 then + 'm' else '' end + COLUMN_NAME end, * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table and (isnull(@target_cols, '') = '' or @include = case when charindex(COLUMN_NAME, @target_cols) > 0 then 1 else 0 end) ) T where variable is not null) T2-- modify the declaration list and assignment listselect @declaration = 'declare ' + left(@declaration, len(@declaration)-1) + char(13), @read_record = 'select top 1 ' + left(@read_record, len(@read_record)-1) + ' from ' + @table + ' where 1=2' + char(13), @write_record = 'update ' + @table + ' set ' + left(@write_record, len(@write_record)-1) + ' where 1=2' + char(13)print @declarationprint @read_recordprint @print_resultprint '-- ' + @write_recordprint '--------------------------------------------------------------------------------------'exec(@declaration + @read_record + @print_result)/*declare @ijob_id SMALLINT,@cjob_desc VARCHAR(50),@imin_lvl TINYINT,@imax_lvl TINYINT^Mselect top 1 @ijob_id = job_id,@cjob_desc = job_desc,@imin_lvl = min_lvl,@imax_lvl = max_lvl from jobs where 1=2^Mprint '@ijob_id = ' + isnull(cast(@ijob_id as varchar), 'null')^Mprint '@cjob_desc = ' + isnull(cast(@cjob_desc as varchar), 'null')^Mprint '@imin_lvl = ' + isnull(cast(@imin_lvl as varchar), 'null')^Mprint '@imax_lvl = ' + isnull(cast(@imax_lvl as varchar), 'null')^M-- update jobs set job_id = @ijob_id,job_desc = @cjob_desc,min_lvl = @imin_lvl,max_lvl = @imax_lvl where 1=2^M--------------------------------------------------------------------------------------@ijob_id = null@cjob_desc = null@imin_lvl = null@imax_lvl = null*/
