I need to generate dynamic TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need to generate dynamic TSQL

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-08 : 12:15:29

I have a query written that successfully pulls the status of record completion. The problem is that to gather the information I can't just count the number of columns that have data and divide by the total number of columns. Instead I have to selectivley choose which columns "count" and which ones don't. I can set this up easy enough but in the future if project stakeholders want to change what columns count in the completion status, it will be a hairy mess to accuratley correct.

Thus I have landed on making the query dynamic. I've seen it done, but haven't built one myself. I am hoping I can get some help. Here is my static query with an explanation below:


use [360dashboard]
select 
	GCO_001, 
	GSL_001,
	'Touched' = 
		CASE
			WHEN 
				(GSL_002 <> '' and GSL_002 is not null) or	(GSL_003 <> '' and GSL_003 is not null) or
				(GSL_004 <> '' and GSL_004 is not null) or	(GSL_005 <> '' and GSL_005 is not null) or
				(GSL_006 <> '' and GSL_006 is not null) or	(GSL_007 <> '' and GSL_007 is not null) or
				(GSL_008 <> '' and GSL_008 is not null) or	(GSL_009 <> '' and GSL_009 is not null) or
				(GSL_010 <> '' and GSL_010 is not null) or	(GSL_019 <> '' and GSL_019 is not null) or
				(GSL_011 <> '' and GSL_011 is not null) or	(GSL_012 <> '' and GSL_012 is not null) or
				(GSL_013 <> '' and GSL_013 is not null) or	(GSL_014 <> '' and GSL_014 is not null) or
				(GSL_015 <> '' and GSL_015 is not null) 
			THEN 1
			ELSE 0
		END,
	'Percent Completed' = 
		(CASE WHEN (GSL_002 <> '' and GSL_002 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_003 <> '' and GSL_003 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_004 <> '' and GSL_004 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_005 <> '' and GSL_005 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_006 <> '' and GSL_006 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_007 <> '' and GSL_007 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_008 <> '' and GSL_008 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_009 <> '' and GSL_009 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_010 <> '' and GSL_010 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_019 <> '' and GSL_019 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_011 <> '' and GSL_011 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_012 <> '' and GSL_012 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_013 <> '' and GSL_013 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_014 <> '' and GSL_014 is not null) THEN 1 ELSE 0 END +
		CASE WHEN (GSL_015 <> '' and GSL_015 is not null) THEN 1 ELSE 0 END)
			/
		15.00
from vw_supplier
order by GCO_001, 'Percent Completed' desc, GSL_001

The first two columns serve only as identifiers to each returned record. They can be ignored. The 3rd column identifies whether a record has been "touched" or, filled out at all. It just checks to see if the values are blank or not. As long as at least one value exists it qualifies as "touched". I would prefer to have this statement built dynamically. Is it best to statically name the columns I DON'T want to check?

The 4th column checks each value and assigns a 1 to the ones with values, and then divides by 15, the count of columns. Would love to have these generate dynamically as well.

Thanks for the help. I'm happy to provide additional information about table structure if that helps.

Craig Greenwood

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 13:05:14

The solution isn't just to take that posted query and make it a dynamic query i.e. put into a variable and execute it.
The main thing is: If you want to build that query dynamic then where is the variable part of the query? Where does the input, for example columns to check or not, come from?
Otherwise you can take your static query and change it to your needs every time you want.

No, you're never too old to Yak'n'Roll if you're too young to die.

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-08 : 13:25:34

Yes, you're right. The dynamic part is that in the future more columns may be added or deleted from the table. In that event, this query would need to be updated, which is a pain. In addition, I have about 10 tables that I need to check the completion on. That is the reason why I want this to dynamically get the correct columns.

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-08 : 13:59:34

Here's what I have so far. It's crashing, but I think this is a good starting point.

[CODE]
DECLARE @sql nvarchar(max)
set @sql = '
use [360dashboard]
select
GCO_001, GSL_001
''Touched'' =
CASE
WHEN
('
select top 1 c.name
from sys.columns c left join sys.objects s on c.object_id = s.object_id
where s.name='vw_supplier'
and c.name not in ('supplierId','projectId','description','GCO_001','GSL_001','GSL_016','GSL_017','GSL_018','GSL_019','GSL_020','GSL_021','active')
' <> '''' and '
select top 1 c.name
from sys.columns c left join sys.objects s on c.object_id = s.object_id
where s.name='vw_supplier'
and c.name not in ('supplierId','projectId','description','GCO_001','GSL_001','GSL_016','GSL_017','GSL_018','GSL_019','GSL_020','GSL_021','active')
' is not null)
THEN 1
ELSE 0
END
from vw_supplier'

print @sql
[/CODE]

I am starting with the "touched" column. It's crashing on my use of apostrohpes. Gotta figure that out, then make the loop work right. It's impractical to select the top 1 column, but this will get me started and gives me a chance to get the syntax right. That's my plan anyway!

Craig Greenwood

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-08 : 14:03:51

quote:
Originally posted by craigwg

In that event, this query would need to be updated, which is a pain. In addition, I have about 10 tables that I need to check the completion on. That is the reason why I want this to dynamically get the correct columns.

That is not a good enough reason to implement it this way. Do you understand the performance and security implications of dynamic SQL?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-08 : 14:05:14

Read this fully: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources