用3GL变量读写数据表记录

use pubs
go

declare
    @table nvarchar(100),
    @target_cols nvarchar(4000),
    @include bit -- include or exclude target columns ?
select @table = 'jobs', @target_cols = '', @include = 1

-- START
declare @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 list
select
    @declaration = @declaration + declaration,
    @read_record = @read_record + read_record,
    @write_record = @write_record + write_record,
    @print_result = @print_result + print_stmt
from
(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 list
select
    @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 @declaration
print @read_record
print @print_result
print '-- ' + @write_record
print '--------------------------------------------------------------------------------------'
exec(@declaration + @read_record + @print_result)

/*
declare @ijob_id SMALLINT,@cjob_desc VARCHAR(50),@imin_lvl TINYINT,@imax_lvl TINYINT^M
select top 1 @ijob_id = job_id,@cjob_desc = job_desc,@imin_lvl = min_lvl,@imax_lvl = max_lvl from jobs where 1=2^M
print '@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

*/