Converting a SProc to dynamic SQL

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

SQL Server 2008 Forums

Transact-SQL (2008)

Converting a SProc to dynamic SQL

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2011-06-17 : 13:20:16

Hi,

I'm refining a search query and opted to move towards a dynamic sql approach. I'm having trouble converting some areas of the SPROC.

cad = table with just userID that I am joining the Athletic table onto.

Original


(@athids is null or exists(select athID from Athletic aa where aa.userID = cad.userID and aa.rating >= isNull(@aSkillRating,rating) and  ',' + @athids + ',' like '%' + cast(aa.athID as varchar(3)) + ',%'))

My attempt at converting it which doesn't work.


SET @SQL = @SQL + CASE WHEN @athIDs IS NOT NULL
THEN ' AND Exists(select athID FROM Athletic aat WHERE aat.userID = d.userID and aat.rating >= isNull(' + @aSkillRating + ',rating) AND athleticIDS like '% cast(athID as varchar(3)) + '%')
ELSE '' END

I'm passing a comma delimited list of athIDs as well as a rating id. So each of those athids must satisfy the minimum rating criteria.

I hope this is enough information.

thank you.

quote:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-17 : 13:50:31

Show us the output of @SQL.

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

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-17 : 14:51:27

try this


SET @SQL = @SQL + 
	CASE WHEN @athIDs IS NOT NULL
		THEN ' AND Exists(select athID 
		                    FROM Athletic aat 
		                   WHERE aat.userID = d.userID 
		                     and aat.rating >= isNull(' + @aSkillRating + ',rating) 
		                     AND athleticIDS like LIKE ''%' + CAST(@athIDs as nvarchar(13)) + '%'')'
		ELSE '' 
	END

If you don't have the passion to help people, you have no passion

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-17 : 15:57:17

I would not convert that portion to dynamic SQL - especially not the way you are going about it. There are plenty of string splitter utilities available that will convert a delimited list into a table.

I would modify that code to use one of those instead.

If this is the only section where you need dynamic SQL - then I wouldn't even go with dynamic SQL. I would use either a temp table, or CTE to pre-process the list into a table of identifiers and join to that table.

Jeff

robc
Yak Posting Veteran

60 Posts

Posted - 2011-06-17 : 20:13:55

Thank you everyone for your quick replies.

The @SQL contains around 25 other criteria, so I didn't bother posting it here. The rest of it works, it's just instances where I need to draw values from a separate table based off a list that satisfy the rating criteria.

so the @sql simplified would look like this.

SET @SQL = N'SELECT TOP 10240 d.userID FROM Details d JOIN Users u ON d.userID = u.userID WHERE 1=1

@jeff

That does sound like it would be a better approach. I will also look into that. Do you have any good leads for me on articles?

@yosiaz, that didn't seem to work. I got an invalid format specification: '%207%')

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-18 : 12:09:12

Here is an article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Read through the article - and the discussion. There are quite a few options here.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources