新增文章

文章标题分类内容

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【存储过程】 删除

if  EXISTS (select * from sys.objects where object_id = OBJECT_ID(N'dbo.a_Select') AND type in (N'P', N'PC'))
               drop     procedure                                      dbo.a_Select
   go
   
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【存储过程】 a_Select 查询数据

create procedure dbo.a_Select
   
       @Table  varchar(max)         -- set @Table = 'ICClassTableInfo'
      ,@Field  varchar(max) = ''    -- set @Field = 'FClassTypeID,FPage,FCaption_CHS,FCaption_CHT,FCaption_EN,FKey,FFieldName,FTableName'
      ,@where  varchar(max) = ''    -- set @where = 'FClassTypeID = 210000001'
      ,@Order  varchar(max) = ''    -- set @Order = 'FPage,FTabIndex'
   as 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if @where !='' set @where = ' where '+ @where
   if @Order !='' set @Order = ' order by '+ @Order

declare @Sql    varchar(max)  set @Sql = ''
-------------------------------------------------------------------------------------------------------------------------
   if @Field = '' or @Field = '*'  begin
   
      select @Sql = 'select * from '+ @Table + @where + @Order
      
   end else begin
   
      declare @Find   varchar(200)   set @Find  = ','
      
      declare @Start  int            set @Start = 1  
      declare @end    int            set @end   = 1  
      declare @Result varchar(50)

while 1 = 1  
      begin  
         set @Field = REPLACE(REPLACE(@Field,' ',''),char(10)+char(13),'')
         set @end = CHARINDEX( @Find ,@Field ,@Start );  -- 找到 , 的位置
         
         if @end = 0 
         set @end = LEN(@Field) + 1;

if @Start >= @end
         break;

set @Result = SUBSTRING(@Field,@Start,@end-@Start)  -- 截取文字
         set @Start = @end + DATALENGTH(@Find)
         
        -------------------------------------------------------------------------------------------------------------------------
         select @Sql += '+'' ,''''''+  convert(varchar(max),'+ @Result +') +'''''' '+ @Result +'''
         '
      end 
      
      select @Sql = 'select '+ @Field +' ,''
      insert '+ @Table +' ( '+ @Field +' ) select  '+ stuff(@Sql,1,4,'') +' Txt 
      from '+ @Table +'
      '+ @where +'
      '+ @Order
   end
   
   exec( @Sql)

/*
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

exec a_Select @Table = 'ICClassTableInfo'
                ,@Field = 'FClassTypeID,FPage,FCaption_CHS,FCaption_CHT,FCaption_EN,FKey,FFieldName'
                ,@where = 'FClassTypeID = 210000001'
                ,@Order = 'FPage,FTabIndex'
          
   exec a_Select @Table = 'ICClassTableInfo'
                ,@Field = '*'
                ,@where = 'FClassTypeID = 210000001'
                ,@Order = 'FPage,FTabIndex'
       
   exec a_Select 't_User','*','FName=''Administrator'''
   
   exec a_Select 't_User','*',@Order = 'FName'
   
   exec a_Select 't_User',@Order='FName'
   exec a_Select 't_User',@Order='FName',@where='FName LIKE''%a%'''
   
*/
go