leonidas blog - All Comments

re: Hashtable, ListDictionary and HybridDictionary

keithrull — Sat, 18 Aug 2007 21:32:58 GMT

Yup! and HybridDictionary is threadsafe too ;)

re: How to avoid Dynamic SQL in Stored Procedure

leonidas — Thu, 16 Aug 2007 05:59:10 GMT

I used LIKE comparison just for my example only. It can be = or <> rather. And the parameter type can be other type and not varchar.

re: How to avoid Dynamic SQL in Stored Procedure

bonskijr — Thu, 16 Aug 2007 05:22:51 GMT

like what jop suggested checkout sommarskog's article. leverage default parameters to stored proc(devpinoy.org/.../1708.aspx) so that instead of having this:

[code]

WHERE ((@supplierLength > 0 AND S.CompanyName LIKE @supplierName + '%') OR @supplierLength = 0) AND

((@categoryLength > 0 AND C.CategoryName LIKE @categoryName + '%') OR @categoryLength = 0)

[/code]

you can have instead:

WHERE (S.CompanyName LIKE @supplierName + '%' OR @supplierName IS NULL )

AND (C.CategoryName LIKE @categoryName + '%' OR @categoryName IS NULL)

with the advantage of just calling your stored proc:

EXEC mySample @supplierName = 'ACME'

EXEC mySample @categoryName = 'TIME BOMB'

Having said that however, the irony of this is that the best way to do a dynamic search(imo) is to use dynamic t-sql not by using stored proc but parameterized dynamic sql passed by your client to sql. While the solution, even with sommarskog's is great and all it will be unwieldy once you have additional search parameters and will be PITA to maintain.

What I did using NH or any solution was to have a SearchCriteria class with all the search params as properties and every setter appends to the internal sql string which builds those parameters as either hQl or sql and execute it.

hth

re: How to avoid Dynamic SQL in Stored Procedure

leonidas — Thu, 16 Aug 2007 03:24:28 GMT

Thanks jop. I will check that later.

re: How to avoid Dynamic SQL in Stored Procedure

jop — Thu, 16 Aug 2007 03:16:35 GMT

For dynamic searches, I find this article very useful: [www.sommarskog.se/dyn-search.html]. The same author (an SQL Server MVP) also has an article about dynamic SQL. Visit that too. [jop]

re: Looking at Maintainability

jop — Fri, 10 Aug 2007 06:18:47 GMT

Yep...

Make it work

Make it right.

And if necessary, Make it fast.

re: Looking at Maintainability

lamia — Fri, 10 Aug 2007 06:05:52 GMT

The first thing I discovered was to put codes into modules(in VB6). Then I learned how to create Types, those c-structs look-alike, that's what they're called right? Yes, optimization should come last(IMHO). For most experienced programmers though, they could make optimized code w/out sacrificing maintainability.

I read somewhere on the internet that you should optimize algorithm and not code. Though when you really think of it, you still have to optimize code. :)