Technical Article

Dynamic Select Generator

,

Query to create dynamic Select statements for automation process.

The Query will create a query assuming data flows from a sql 2005 and up

or from sql 2005 and below. It cast the xml and varcha(max) to text fields.

/*******Create Dynamic Select Qry***********
 ****Jorge L. Novo DBA********************/ 
 /**** Variables*******/declare @TableName varchar(80) ---Name of table 
declare @Qtry varchar (8000) ----Final Qry Result
declare @Column varchar (8000)----Columns of Qry
declare @IsSql2000 int --- SQL 2005 to 2000 Indicator

/***********Set Values *******/set @Qtry = 'Select '  
set @TableName = 'TableNameHere' -----Table Name for Qry
set @IsSql2000 = 1 ----SQL2000 Indicator 0 = moving data from sql2005 and above, 1= moving data from 2005 and below

/****Lets the magic begin******/select c.name as ColumnName,c.colid,t.name as ctype,c.length as clength 
into #Qrty from syscolumns c
inner join sysobjects o
on c.id = o.id
left outer join systypes t
on t.xtype = c.xtype 
where o.id = object_id (@TableName)
order by c.colid
/****Now the real work begin ****/if @IsSql2000 = 1
 begin 
    select @Column = 
    Coalesce(@column+',',' ')+
case when ctype = 'xml' then 'Cast(Cast( ['+ColumnName+'] as varchar(max))as text) as ['+ColumnName+']' else case when ctype ='varchar' and clength < 0 then 'Cast(['+ColumnName+'] as text)' else '['+ColumnName+'] as ['+ColumnName+']' end end 
from #Qrty
  end 
else
  begin 
  select @Column = Coalesce(@column+',',' ')+ '['+ColumnName+']'
 from #Qrty
 end 
 select @Qtry = @Qtry + @Column + ' From '+@TableName+' (Nolock)'
  select @Qtry

drop table  #Qrty
print 'See U later alligator'

Rate

2.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.67 (3)

You rated this post out of 5. Change rating