I went to the Richmond SQL Server User Group meeting last week, and as always its a good place to pick up new ideas or to re-think old ones.  Jennifer Kenny was speaking about expanding your ideas when it comes to solving SQL issues stressing set based approaches to iterative ones.    Long story short it got me thinking about how I’ve been building dynamic SQL strings.  For one reason or another I write a lot of code that writes code that writes code, I believe the fancy name is meta programing.  I’ve done this for a while but in the past 18 months its really picked up.  I the main basis for this is system tables and a few scant meta tables created for whatever project I’m working on.  I used to loop through sys.tables then loop through sys.columns or sys.indexes or sys.index_columns or whatever I needed to pull all of the necessary metadata.  To a certain extent I still do.  Here is an example of how I used to build my dynamic SQL.

SET NOCOUNT ON;
go
USE msdb
go
/*-----Loop based string concatenation-----*/
USE msdb
go
declare @TSQL varchar(max);
declare @TSQL_Select_String char(9) = 'Select '+char(13)+char(10);
declare @TSQL_Table_Name sysname='BackupFile';
declare @TSQL_From_String varchar(300) = 'From '+@TSQL_Table_Name
declare @Column sysname;
declare @TSQL_Columns_List varchar(max)='';
 
declare Columns_Cursor cursor fast_forward for
select
c.name
from
sys.columns c
where
c.object_id=object_id(@TSQL_Table_Name)
order by
c.column_id ;
 
open Columns_Cursor;
fetch next from Columns_Cursor into
@Column;
 
while @@FETCH_STATUS =0
BEGIN
 
	set @TSQL_Columns_List = @TSQL_Columns_List+@Column+','+CHAR(13)+CHAR(10)
	fetch next from Columns_Cursor into
	@Column;
 
END
close Columns_Cursor;
deallocate Columns_Cursor;
 
set @TSQL_Columns_List =left(@TSQL_Columns_List,LEN(@TSQL_Columns_List)-3)+CHAR(13)+CHAR(10);
set @TSQL=@TSQL_Select_String +@TSQL_Columns_List +@TSQL_From_String;
 
print @TSQL;
execute (@TSQL);
 
go

Now the above example works and creates a simple select statement naming all of the columns in a table.  If you look at its basically just building a list of columns by iterating through a cursor and concatenating a string.  The better way in my opinion is do the following:

/*-----Set based string concatenation-----*/
declare @TSQL varchar(max);
declare @TSQL_Select_String char(9) = 'Select '+char(13)+char(10);
declare @TSQL_Table_Name sysname='BackupFile';
declare @TSQL_From_String varchar(300) = 'From '+@TSQL_Table_Name
declare @TSQL_Columns_List varchar(max)='';
select
@TSQL_Columns_List +=c.name +','+CHAR(13)+CHAR(10)
from
sys.columns c
where
c.object_id=object_id(@TSQL_Table_Name)
order by
c.column_id 
 
set @TSQL_Columns_List =left(@TSQL_Columns_List,LEN(@TSQL_Columns_List)-3)+CHAR(13)+CHAR(10)
set @TSQL=@TSQL_Select_String +@TSQL_Columns_List +@TSQL_From_String;
 
print @TSQL;
execute (@TSQL);

Now at the end of the day this produces the same result but if you look at the way the string is being built it comes off cleaner.  (At least to me.) The big take a way and what makes this possible is the += operator active on the @TSQL_Columns_List variable.  One active on a set of data it returns the entire result and adds it to itself and the current value of the variable.  This is a great way to compile all of the results at once to a variable. It also removes at least one set of while or cursor loops. You may still need a looping mechanism depending how many levels deep you are going or how complex your dynamic sql is but it can help cut down on the clutter quickly. When you integrate a UDF – Inline Table Value Function things can get really exciting but that’s for another day.