Av rating:
Total votes: 11
Total comments: 6

 send to a friend
 printer friendly version

Like a Phoenix, the dynamic SQL versus canned procedures and user functions argument has resurfaced on the SQL newsgroups. Many of the proponents of the dynamic argument are web or Access developers, or developers of some other front end. Developers in the canned camp tend to be DBAs or developers with some depth of knowledge in stored procedures and functions.

My characterization has, of course, loaded the dice in favor of the latter group, of which I consider myself a member. After exploring some of my reasons and experiences, I’ll cite a simple example to illustrate my conclusion that canned SQL is always the better option.

Many of the forms of dynamic SQL I use in my web and client applications enable the user to specify complex selection combinations. In fact, there might be 20 such controls on the selection form.

Given this complexity, I have no choice but to examine each control in the front end. If it is not null, then I create a clause in the SELECT statement that corresponds to the column and the value specified. I build up the statement control by control, then issue it.

Conversely, canned SQL is available to any and all front ends that need it, without duplicating the logic in each front end. It also runs more quickly and precludes SQL injection almost entirely.

I admit that in some instances there is no alternative but to write dynamic SQL statements and send them to the back end for execution. But in my experience, these instances are few and far between.

Below is an example that uses only three controls, but should be sufficient to illustrate my point. It also reveals a cool syntax trick that you can apply to your own projects.

Suppose your application presents a form with three lists, from which the user can select a maximum of one item from each. Let’s also suppose that this form talks to a single table—or view or UDF—with columns A, B and C.

The possible combinations of the three columns are:

/****** Object: Table [dbo].[ABC] Script Date: 01/07/2006 20:22:20 ******/
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC]
( 

  [PK] [int] IDENTITY(1,1) NOT NULL,
  [A] [int] NOT NULL,
  [B] [int] NOT NULL,
  [C] [int] NOT NULL,  
CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED 
(  
  [PK] ASC  
)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) 
ON [PRIMARY]

Here are a few sample rows:

PK  A B C 
 
1   1 1 1
2   1 2 1
3   1 2 3
4   2 1 1
5   2 2 1
6   2 2 2
7   3 1 1
8   3 1 2
9   3 2 1
10  3 2 2
11  3 2 3
12  3 3 3
13  2 2 3
14  2 3 2
15  1 3 2
16  1 1 3

Here is the code to create the table function:

— ============================================= 
— Author: Arthur Fuller 
— Create date: 2006/01/07 
— Description: Optional Parameters 
— ============================================= 
CREATE FUNCTION [dbo] .[ABC_fnt] 
( 

  — Add the parameters for the function here 
  @A int, 
  @B int, 
  @C int 

) 
RETURNS TABLE 
AS 
RETURN 
( 

  — Add the SELECT statement with parameter references here 
SELECT * 
FROM ABC 
WHERE 

    (A = @A OR @A IS NULL)  
  AND  
    (B = @B OR @B IS NULL) 

  AND 
    (C = @C OR @C IS NULL) 

)     

The trick in this function is the comparison between the expected parameters and themselves. Think about the logic for a moment before proceeding.

Finally, here are several sample calls:

SELECT * FROM dbo .ABC_fnt ( NULL, 1 , 1 ) 
SELECT * FROM dbo .ABC_fnt ( 1 , 1 , 1 ) 
SELECT * FROM dbo .ABC_fnt ( NULL, NULL, 1 ) 
SELECT * FROM dbo .ABC_fnt ( 1 , NULL, 1 ) 
SELECT * FROM dbo .ABC_fnt ( NULL, NULL, 2 ) 
SELECT * FROM dbo .ABC_fnt ( 1 , NULL, 3 ) 
 

My simple example uses only three arguments and assumes exact matches on each specified argument. But it should be obvious that you can do the same thing with 20 arguments. With a little more work, the function can be modified to handle LIKE arguments and their converse as well.

My point is not that this approach will cover every possible situation, but that it will cover many situations in which you might have used dynamic SQL instead. By now, the gains should be obvious.