Hi everyone!  Yes, I have started blogging again.  I'm over on pentonizer.com now.  Please join me there.

Suppose you need to exclude or include parameters to a sql statement.  Let's take this example:

CREATE PROCEDURE dbo.myProc
    @pkA int
    , @pkB varchar(10)
    , @pkC decimal(12, 2)

AS

SELECT
    t.colA, t.colB, t.colC
FROM
    dbo.myTable t
WHERE
    t.pkA = @pkA
    AND t.pkB = @pkB
    AND t.pkC = @pkC

This procedure expects three parameters, as does the sql statement.  So we want to omit the parameters based on being set to NULL.  There are several ways to do this:

1. Use dynamic sql in your client language
2. Use dynamic sql in your stored procedure
3. Use conditional logic in your query with various combinations of OR, AND or COALESCE/ISNULL in your query
4. Use structured dynamic sql in a stored procedure

Let me give an example of each:

1. Build the sql in a string such as this:
   
   string sql = “SELECT colA FROM tblA“;
   if(prmB != null && prmB.Length != 0) sql += “ WHERE colB = '“ + prmB.Replace(“'“, “''“) + “'“; 
   
   You have to contend with ensuring that your logic in the final query is correct.  This is easy with a single parameter, but when you end up having more than one in your WHERE clause (or other clauses) then the logic can get quite complex.  We also needed to deal with SQL Injection.  Of course, this is more easily dealt with by using parameters on command objects (like using a SqlParameter with a SqlCommand object). 
2. This is similar to the above but you are doing the logic in the stored procedure.  You still need to contend with sql injection in this case.
3. Conditional logic in your query means resorting to using OR and AND when needed:
   
   SELECT colA FROM tblA
   WHERE (colB = @colB OR @colB IS NULL)
   
   -- OR --
   
   SELECT colA FROM tblA
   WHERE colB = COALESCE(@colB, colB)
   
   This suffers from branching logic in your query.  It becomes more an more difficult for SQL Server to make a realistic execution plan for this type of query, especially when more conditions are present.
4. Use structured dynamic sql in your procedure:

   CREATE PROCEDURE dbo.myProc
       @pkA int
       , @pkB varchar(10)
       , @pkC decimal(12, 2)
   
   AS
   SET NOCOUNT ON 
   
   DECLARE @sql nvarchar(4000) 
   
   SET @sql = N' 
   SELECT 
     t.colA, t.colB, t.colC 
   FROM 
     dbo.myTable t 
   WHERE 
     1 = 1 
     @pkAREP 
     @pkBREP 
     @pkCREP'
   
   IF @pkA IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkAREP', 'AND a.pkA = @pkA')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkAREP', '')
   
   IF @pkB IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkBREP', 'AND a.pkB = @pkB')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkBREP', '')
   
   IF @pkC IS NOT NULL
     SET @sql = REPLACE(@sql, '@pkCREP', 'AND a.pkC = @pkC')
   
   ELSE
     SET @sql = REPLACE(@sql, '@pkCREP', '')
   
   
   EXECUTE "dbo"."sp_executesql" 
     @sql 
     , N'@pkA int, @pkB varchar(10), @pkC decimal(12, 0)' 
     , @pkA = @pkA 
     , @pkB = @pkB 
     , @pkC = @pkC 
   
   RETURN 0
   

I want to discuss option #4 above.  I believe it to do a number of things for the developer:

1. Preventing SQL injection because you are using parameterized queries
2. Caching query plans because you are using sp_executesql
3. Controlling the data coming in to the procedure
4. Preventing branching logic in your WHERE clause in the query

I actually would do a little more with the '1=1' part, but for clarity in this post I am not.  Also, in my queries I typically have values that are always required so the '1=1' isn't necessary.  Of course, as the developer you need to ensure that you don't inadvertantly put any sql injection points in your dynamic portions of your sql.  But this would get caught in a code review, right?  ;-)

First off....I know I badly need to blog more.  Life just keeps running me over (enough of the excuses).  My friend Steve Schofield wanted me to blog more on inserting XML into SQL Server.  So, I am going to post a number of things.  All that actually work by downloading the files.

[1] Table layout (dbo.TestMessages.TAB)
[2] Stored Procedure (dbo.InsertMessageXml.PRC)
[3] C# code with some XML

I am also posting a zip of everything (compiled EXE also).  Please change XmlInsert.exe.config file to point to your database.  Then, take the dbo.InsertMessageXml.PRC and dbo.TestMessages.TAB files and execute them to put those objects in your database.

These files are based on the code in my previous blog entry.

In case anyone *hates* the "My eBooks" folder that gets created in "My Documents" when
Adobe Acrobat opens, here is the *fix*:

http://www.annoyances.org/exec/forum/winxp/n1072006739

My...that thing was annoying!

I thought it would be worth mentioning again that there is a compiled help file called “SQL Server 2000 System Table Map” which is right here.  I believe it to be a very valuable documentation tool for the database administrator, as well as the database programmer.  An understanding of the lower levels of a product is always a good thing, no?

Yes, I know it will change for Yukon, but people will be on SQL 2000 for some time still.

So, I have started blogging.  I had been thinking about how and what I would start this off.  It occured to me that I should talk about something that I had learned throughout my life but never knew the name of until college.  Even after that, I wasn't sure ow to describe the methods I take when designing systems.  Encyclopædia Britannica defines the Axoimatic Method:

in logic, a procedure by which an entire system (e.g., a science) is generated in accordance with specified rules by logical deduction from certain basic propositions (axioms or postulates), which in turn are constructed from a few terms taken as primitive

Simply put, we have it as a "system" with "rules".  Looking at this from another angle, each rule could be defined as a system by itself.  I suppose this could be likened to a parent-child relationship in the sense that you could have something like

system
  --> rule1
        --> rule3
        --> rule4
  --> rule2
        --> rule5

where rule1 and rule2 are "systems" themselves.  Enough of this meta-speak...so how do I want to relate this to programming in general?  Actually, I'd say more specifically system design in general.

Suppose your boss comes in to your office/cubicle and tells you "I need a system that will give me a report on widgets in the warehouse.  I need it as soon as possible."  Some paths you can take include [] make a basic system from existing resources based on what your boss told you and [] ask questions.  From the beginning, you have to think about how this is to be designed.  Of course, your design could be a simple, static webpage that you put together on demand (when your boss tells you to).   You could make it where someone enters all of the data into your system.  You might even want to pull data from an existing system.  All somewhat viable options.  But, you of course need to dig deeper into what your boss' vision might be.  You will most likely need some sort of input mechanism.  Possibly some filtering and sorting.  Perhaps different style reports (aggregates and such).  Each of these functional requirements depends on rules for definition.  Each of the functional requirements make up the system you are defining.  The smaller each "rule" is, the easier it is to understand and adjust when needed.  Also, and this is fairly important...the easier it is to reuse the rule anywhere else in the system when appropriate.

This moves into another topic that I'll just mention here: refactoring.  This book is probably one of the most important books to get you started down the path of good system design.

How do you use this in real life?  Start looking at your designs from the perspective that even if your "system" is small, you can add to it easily.  Consider that youmay need to modify bisiness rules in the future.  Make that easier to do without reworking the system design.  Realizing what scope your rules are makes these processes easier.

Apply at least some of these ideas to your current programmatic methods and I'll bet a better system will come of it.