dynamic sql vs normal with 'IN' stmnt

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 2005 Forums

Transact-SQL (2005)

dynamic sql vs normal with 'IN' stmnt

Author

Topic

owleabf
Starting Member

2 Posts

Posted - 2007-03-19 : 17:21:18

Hi all-
Have run into a little hiccup and wondering if anyone has any ideas...

Basically I have a function to proximity search by zip code and that returns a varchar(max) of a list of zips in this form:

'35004','35007','35013','35014','35015'

The problem occurs when I try and check if a postalcode is in that list... if I add this to my where clause it returns no results:


declare @zipCodesWithinProximity varchar(max)
SET @zipCodesWithinProximity = dbo.fnct_FindNearby2(@clubZip,@proximity)
...
WHERE (ca.postalcode IN (@zipCodesWithinProximity))

I've been able to get it to work with dynamic sql, but would like to avoid that if possible to not have the performance hit. Here's the dynamic that works:


	IF  @courseZip != ''
	BEGIN
		IF @Where = ''
			SET @Where = ' WHERE '
		ELSE
			SET @Where = @Where + ' AND '

		SET @Where = @Where + 'fa.PostalCode IN (' + dbo.fnct_FindNearby2(@courseZip,@proximity) + ')'
	END

Any ideas? I'm stumped... thanks for whatever help you can give.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 17:39:21

dynamic sql won't give you a performance hit in the long run.
the problem with dynamic sql is security.
so if you can check for malicious code before it enters the sproc do use dynamic sql.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-19 : 17:44:50

you can use a technique like this to parse csv values:
http://www.sqlteam.com/item.asp?ItemID=2652

forgot about these two. there are very complete discussions here:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html

www.elsasoft.org

owleabf
Starting Member

2 Posts

Posted - 2007-03-19 : 17:54:20

quote:
Originally posted by spirit1

dynamic sql won't give you a performance hit in the long run.
the problem with dynamic sql is security.
so if you can check for malicious code before it enters the sproc do use dynamic sql.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Really? Huh... was this a performance hit in 2k or was I just making the performance hit up?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 17:59:04

well in 2k5 it's not anymore.
in 2k it was a perf hit because of cached exec plans.
2k5 has this improved to the point where the hit is irrelevant.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-19 : 19:02:49

Dynamic SQL in the proc will require SELECT (possibly other DML) permissions on the accessed objects for the user.
If the policy is to only allow access by stored procedures this might require a policy/security breach.

rockmoose

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-19 : 23:30:16

Why the hell do you have a function that returns a string of proximities and not a result set you can plug straight into your query? Either rewrite the function to return a proper set or add the chunk of code into your IN statement.
Dynamic SQL sucks. Dynamic SQL that is dynamic when it need not be sucks even more!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources