sqlserver学习笔记

1、修改列名

exec sp_rename '表明.原列名','新列名','column';

2、处理将查询出来的内容作为字段再进行查询方法(拼接字符串然后执行)：
select @sql='insert into #rst  select '''+@ipq_part+''','''+@tmp_option+''','+@tmp_name+', 备注+'' ''+W系列+str(扎线圈数)from t_ipqc$'

exec(@sql)

3、sqlserver存储过程解密

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER  procedure mssp_get_usermn  
    @user      varchar(30)
as  
begin  
    set nocount on


    declare @tm_id      varchar(30),
            @tm_par     varchar(30),
            @tm_name    varchar(30),
            @tm_path    varchar(100),
            @tm_ico     varchar(100),
            @tm_perm    varchar(255),
            @tm_class   varchar(30),
            @allow      bit




    create table #rst
    (
    r_id      varchar(30)     not null,
    r_par     varchar(30)     not null,
    r_name    varchar(30)     not null,
    r_path    varchar(100)    not null,
    r_ico     varchar(100)    not null,
    r_class   varchar(30)     not null
    )


    declare cur_01 cursor local static scroll for
    select mn_id,mn_par,mn_name,mn_path,mn_ico,mn_perm,mn_class
    from mn_mstr
    where mn_type = 'M'
    open cur_01
    fetch first from cur_01 into @tm_id,@tm_par,@tm_name,@tm_path,@tm_ico,@tm_perm,@tm_class
    while @@fetch_status = 0
    begin
        exec check_perm
             @user = @user,
             @perm = @tm_perm,
             @allow = @allow output


        if @allow = 1
        begin
            insert into #rst(r_id,r_par,r_name,r_path,r_ico,r_class)
            values (@tm_id,@tm_par,@tm_name,@tm_path,@tm_ico,@tm_class)
        end


        fetch next from cur_01 into @tm_id,@tm_par,@tm_name,@tm_path,@tm_ico,@tm_perm,@tm_class
    end


    close cur_01
    deallocate    cur_01


    update #rst set r_path = r_path + '?id=' + r_id where r_path <> ''


    select * from #rst order by r_par,r_id


end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


exec sp_decrypt [check_group]