Introduction

Imagine a contacts database where we need to indicate whether or not a particular contact has a particular attribute

of a dynamic list of attributes.

For example our contact may be a student who has to choose a set of topics within a course. We may have a table structure

similar to the one below

• There will be a contact topic record for every combination of contact and topic
• Topics are broken down into different types
• All id fields are integers
• The primary keys are clustered
• The relationships in the diagram are for illustration purposes only. DRI may not necessarily be used, depending on the application.

The web front end for this displays a list of topics for a particular contact with a checkbox next to each description.

The challenge

There are two challenges to deal with here

Firstly, we need an efficient way of updating the records within our Tbl_ContactTopic table.

Secondly the most annoying HTML control on a web form has to be the checkbox. All other controls on a submitted form exist

regardless of whether or not a user has entered any information in those fields, but a checkbox only exists if the user has

checked it.

The web form

There is nothing we can do about the behaviour of the check box, but we can make things a bit easier for ourselves.

Consider the HTML below

<label for="chktopic1">Transact SQL</label>
<input type="checkbox" name="chktopic" id="chktopic1" value="1">
<label for="chktopic2">SQL Administration</label>
<input type="checkbox" name="chktopic" id="chktopic2" value="2">
...
...
<label for="chktopic10">SQL Administration</label>
<input type="checkbox" name="chktopic" id="chktopic10" value="10">

Note that the value attribute contains the primary key value of the topic and that the id

attribute and matching for attribute of the <label> tag has the primary key value

appended in order to force uniqueness of the controls. This is necessary so that screen readers for the blind can interpret the form

By calling all of our checkbox controls chktopic then when we request the value of the chktopic then the checked items will

be returned as an array of values.

What approach should we take?

Take it as read that I am going to use stored procedures and not dynamic SQL for this.

I could get the web server to loop through the array returned by requesting the value of chktopic and call a stored procedure to update

my database for each itteration. The SQL would have a negligible impact on the database server requiring a clustered index seek.

On low to medium traffic web sites there would also be minimal impact. On high traffic sites however even small differences in

performance get multiplied up by the sheer volume of traffic.

In this case we may be better off passing our array of values across and letting the database server take the load and leaving the web

server(s) to do what web servers do best.

Web server code

To pass my array of checkbox values I need to cast the array as a string.

In the live system this was using server side javascript. (SSJS) so the code looked something like the following.

var _contact;
var _topic="";
if(request.chktopic){
// Cast the array of chktopic values to a single comma delimited string.
_topic=new String((request.chktopic);
}
// If there isn't a contact id then there must be an error
if(!(_contact=request.contactid)){
redirect("errorpage.htm");
}
//Code to handle _singleTopic, _topicType and _isMember values here
var db=dbatabase.clone();
// Execute the stored procedure and don't cache the results.
var sql="!nocache:execsp usp_setcontacttopic "
+ _contact
+ ", "
+ _singleTopic
+ ", "
+ _topicType
+ ", "
+ _isMember
+ ", '"
+ _topic
+ "'";
var result=db.execute(sql);
/*
Other code plus error trapping
*/
db.release();// Always destroy objects explicitly.

Assume that there is a considerable amount of error trapping over and above the code snippet shown.

SQL server code

Firstly I need a function to break out a delimited list into a table of values. I have called my function fnSplitter,

the code for which is shown below.

CREATE FUNCTION dbo.fnSplitter ( @sInputString1 VARCHAR(8000) , @sSplitChar CHAR(1))
RETURNS @tbl_List TABLE (Id Int PRIMARY KEY )
AS
/*
*FUNCTION:fnSplitter
*DESCRIPTION:Accepts a string of integer values with some form of delimiting character
*and returns  a table with a field containing a list of values.
*ARGUMENTS:@sInputString1The 1st string containining the list of integer values.
*@sSplitCharThe character that is used as a delimiter.
*REMARKS:The function assumes that the string of integer variables does not contain duplicates.
*
*DateAuthorDescription
*============================
*27-Jun-2002David PooleCreated
*/
BEGIN
  DECLARE@lInputStringLength1Int ,
   @lPosition1Int,
   @lSplitChar1Int,
   @lIDInt

  SET@lInputStringLength1 = LEN ( @sInputString1 )
  SET @lPosition1=1
  SET@lSplitChar1=1

  WHILE @lPosition1 BEGIN
      SET @lSplitChar1 = CHARINDEX ( @sSplitChar , @sInputString1 , @lPosition1)
      IF @lSplitChar1 = 0
       BEGIN
         SET@lID = CAST( SUBSTRING( @sInputString1 , @lPosition1 ,1+ @lInputStringLength1 - @lPosition1) AS Int )
         SET @lPosition1= @lInputStringLength1 + 1
       END
      ELSE
       BEGIN
         SET @lID = CAST ( SUBSTRING( @sInputString1 , @lPosition1 , @lSplitChar1 - @lPosition1) AS INT )
         SET @lPosition1 = @lSplitChar1+1
       END
      INSERT @tbl_List( Id)
        VALUES( @lID)
     END
  RETURN
END

Of course once you have decided to use this technique then you will find that you will want to use it in other situations.

For this reason it makes sense to put this function in the MODEL database as well as your current database.

Obviously my database server code has to include the usp_setcontacttopic stored procedure that was

called by the web server code.

As you can see it accepts up to 5 arguments. This implies that the procedure can do a great deal more than

has been discussed so far.

As an aside, it is easier to sell the concept of stored procedures if the programmer is shown a clear advantage in

using them, and that advantage has to be to the programmer, not some abstract such as user performance.

Most programmers are used to the concept of calling a function with varying argument lists so it is easy to

sell the idea of a single stored procedure performing multiple tasks than many stored procedures performing single tasks.

An explanation of the arguments is shown below

The full code for the procedure is shown below

CREATE PROC dbo.Usp_SetContactTopics
@ContactIdInt = NULL ,
@TopicIdInt = NULL ,
@TopicTypeIdInt = NULL ,
@IsSelectedBIT = NULL ,
@TopicArrayVARCHAR(200)= NULL
AS
/*
* PROC:- Usp_SetContactTopic
* Description:-For a given contact record the IsSelected flag can be set for
*               a) All topics.
*               b) All topics of a specified type.
*               c) A specific topic.
*               d) A list of topics
*
*Arguments:-
*     @ContactId - The Id of the contact within the contact/topic record to be retrieved.
*     @lTopicId - The id of the topic within the contact/topic record to be retrieved.
*     @lTopicTypeId - The id of the topictype for which all records will be set.
*     @IsSelecte - The value of the IsSelected flag.
*     @TopicArray - A list of topic ids to be set.
*
*   Variables
*     @lReturnValue - Used to store @@ROWCOUNT, otherwise will contain -1.
*
*   Dependancies
*     Table:
*       dbo.Tbl_ContactTopic
*       dbo.Tbl_Topic
*
*DateAuthorDescription
*============================
*05-Mar-2004David PooleCreated
*/
SET NOCOUNT ON
--Check that all compulsory parameters have been supplied.
IF @ContactId ISNULL OR @IsSelected ISNULL
 BEGIN
    RAISERROR ('A contact and setting must be specified.',16,1) 
    RETURN 0--Return value zero indicates and error.
 END
DECLARE@lReturnValue Int
--Cater for NULL arguments
SET@TopicId = ISNULL(@TopicId,0)
SET@TopicTypeId= ISNULL(@TopicTypeId,0)
--If a specific topicid has been specified then there is no point
--checking other parameters as it indicates a single specific record.
IF @TopicId > 0
 BEGIN
   UPDATEC
     SETC.IsSelected = @IsSelected 
    FROMdbo.Tbl_ContactTopic AS C
    WHEREC.ContactId = @ContactId
    ANDC.TopicId= @TopicId 
   SET @lReturnValue = @@ROWCOUNT
   --There should never be a situation where no rows are updated therefore
   --return a special -1 value to indicate a failure.
   IF @lReturnValue = 0
   SET @lReturnValue=-1
   RETURN@lReturnValue
 END
SET@TopicArray=LTRIM(RTRIM(ISNULL(@TopicArray,'')))
--If no array of Topic Ids been submitted
IF LEN(@TopicArray)=0
 BEGIN
   --Set all topics of a particular type
   IF @TopicTypeId>0
    BEGIN
      UPDATEC
        SETC.IsSelected = @IsSelected 
       FROMdbo.Tbl_ContactTopic AS c
         INNER JOIN dbo.Tbl_Topic AS T
           ON C.TopicId = T.TopicId
       WHEREC.ContactId = @ContactId
       AND T.TopicTypeId = @TopicTypeId
 
      SET @lReturnValue = @@ROWCOUNT
      IF @lReturnValue = 0
        SET @lReturnValue=-1

      RETURN @lReturnValue
    END
 END
ELSE
--There is an array of ids
 BEGIN
   -- Combine the array with a topic type filter.
   IF @TopicTypeId>0
    BEGIN
      UPDATE C
        SETC.IsSelected = @IsSelected 
       FROMdbo.Tbl_ContactTopic AS C
         INNER JOIN dbo.Tbl_Topic AS T
           ON C.TopicId = T.TopicId
         INNER JOIN dbo.fnSplitter(@TopicArray,',') AS FN
           ON C.TopicId = FN.ID
AND T.TopicId = FN.ID
       WHEREC.ContactId = @ContactId
       AND T.TopicTypeId =@TopicTypeId 
      
      SET @lReturnValue = @@ROWCOUNT
      IF @lReturnValue = 0
        SET @lReturnValue=-1
  RETURN@lReturnValue
    END
   ELSE
    --There is only an array of topics to update.
    BEGIN
      UPDATEC
        SET  C.IsSelected = @IsSelected 
       FROM dbo.Tbl_ContactTopic AS c
         INNER JOINdbo.fnSplitter(@TopicArray,',') AS FN
            ONC.TopicId = FN.ID
       WHEREC.ContactId = @ContactId
      SET @lReturnValue = @@ROWCOUNT
      IF @lReturnValue = 0
        SET @lReturnValue=-1

      RETURN@lReturnValue
    END
 END
--If we reached this point then we know that only the contact has been specified
--so set all topic records for the contact.
UPDATE  C
  SET C.IsSelected = @IsSelected 
 FROMdbo.Tbl_ContactTopic AS c
 WHEREC.ContactId = @ContactId
SET @lReturnValue = @@ROWCOUNT
IF @lReturnValue = 0
  SET @lReturnValue=-1
RETURN@lReturnValue
GO

Testing the procedure

In order to test the procedure I developed a test plan as follows.

Conclusion

The procedure looks complicated, but if you look carefully you will see that it is really a lot of simple UPDATE

statements that are triggered depending on the arguments supplied.

The execution plans for the different scenarios show that index seeks are the order of the day so even when a delimited list

of ids are passed to the procedure the cost is low.

The amount of code and corresponding processing on the web server is reduced.

The programmer only has to check the arguments before passing them to a predefined stored procedure, thus saving themselves the bother

of coding a fairly mundane routine.