I have been keeping up with the plethora of posts in this debate over the last week originating on Frans Bouma and Rob Howard's Blogs.
Some other people, who's opinions I respect greatly have weighed in on the subject and I was thinking that there really is only one reason I ever use Dynamic SQL.
That reason is to perform Dynamic Where Clauses.
Today while revisiting my own code to try to get RID of my Dynamic SQL, I discovered through the AZDNUG mailling list and this SQLTeam article a couple methods that I had not thought about to perform the task.
Both methods have you create your parameters with a default value of NULL and then test for null in the Where clause. I hadn't thought of doing it that way, but it makes perfect sense. Just setup all the fields you need to search on as you normally do, but set them to NULL as default, then in your were clause, either use:
WHERE
(@MyParam1 is NULL or MyParam1_Field Like @MyParam1) AND
(@MyParam2 is NULL or MyParam2_Field Like @MyParam2)
or
WHERE
MyParam1_Field = COALESCE(@MyParam1,MyParam1_Field) AND
MyParam2_Field = COALESCE(@MyParam2,MyParam2_Field)
If you need to use "Like" then I suggest you use the first method because It seems to work a little better on the NULL values.
So with that, I now have COMPLETELY removed all Dynamic SQL from my bag of needed tricks for now.
I like treating StoredProcs like methods of my database so keeping them inside the database is a pretty high priority for me, I don't really care either way about the performance implications and don't even care to debate it. My ONLY concern with keeping StoredProcs as my exclusive access method was for consistency in technique and encapsulation of objects. I have been moving most of my business rules into Web Services and this help me to encapsulate where things are done even more. If I have even ONE Dynamic SQL routine then there is cause for confusion as to where I am processing the SQL that is being run against the database.
The other Big Advantage is that for changes to StoredProcs that follow OOP Versioning rules don't require my Business Objects to be recompiled. Security is another reason, but you can deal with most of the security concerns in other ways, however, it does make it nice not to have to worry about it when you are passing only parameters that are never executed as Dynamic SQL.
I don't know when Fritz Onion wrote this, but I sure wish I had found it about 6 months ago...
This is incredibly useful when you want to work with WebApps but are not happy with the way the wizard forces you into making certain assumptions about how you want to layout your app on a local machine.
It also seems to take care of a bunch of annoyances I had with VS.NET Web Folders. I use subversion AnkhSVN and VS.NET really hates using .svn folders. This eliminates the problem with VS.NET not wanting to refresh the folders, etc., etc.
One caveat about the document, it was a little unclear about how you create new VB Web Projects under this scenario, so here is a briefing:
follow the instructions in Fritz's document for adding project wizards.
- Create a New Empty VB Project
- Add a WebForm and a Web.Config
- Open IS Manager and either point your Default Web Site's home directory to this folder, or make a Virtual Directory pointing to it.
- Right-Click your Project and select Properties, then Configuration Properties/Debugging
- Change Start Action to Start URL instead of Start Project and use the URL (not the file location) of your start page, i.e. http://localhost/webform1.aspx
- enable ASP.NET debugging
- Now go to Common Properties, Change the output type from Windows Application to Class Library (feel free to change your root namespaces and Assembly name now that you CAN), apply the settings and close the properties dialog
Hit Start and enjoy your new ability to debug flexibility :-)
I really disliked the way that dotText was screwing up my posts with code in them, and I ran across this great tool, complete with source code written by Jean-Claude Manoli.
I have plugged it into dotText so I can paste in code easily for blog entries and all my previous posts now look right :-)
I think I may just write a simple blog post utility too that includes some of this code, something similar but simpler than w.bloggar that works better with metablogapi... Too many projects, too little time.
Copyright © 2003-2004 H. Steele Price, IV -
All opinions are my own, not necessarily those of my employer, your mother, or any government agency.