Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reducing Round Trips - Working with HTML Checkboxes

By David Poole,

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

Example schema

  • 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:	@sInputString1	The 1st string containining the list of integer values.
*			@sSplitChar	The character that is used as a delimiter.
*	REMARKS:	The function assumes that the string of integer variables does not contain duplicates.
*
*	Date		Author		Description
*	===========	======		===========
*	27-Jun-2002	David Poole	Created
*/

BEGIN
  DECLARE@lInputStringLength1Int ,
   @lPosition1	Int,
   @lSplitChar1	Int,
   @lID	Int


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

  WHILE @lPosition1 <= @lInputStringLength1
    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

ArgumentDescription
@ContactIdThe unique reference number for a particular contact. This argument must always be present.
@TopicId

Optional. The unique reference number for a particular topic. This allows a specific contact/topic combination to be set.

If the value is not set then we could ask the procedure to update all records for a particular topic.

@TopicTypeIdOptional. The unique reference number for a particular topic. This allows all topics of a specific type to be set for a contact.
@IsSelectedMust be set to either 0 or 1.
@TopicArrayOptional. Allows a set of specific topics to be set.

The full code for the procedure is shown below


CREATE PROC dbo.Usp_SetContactTopics
	@ContactId	Int = NULL ,
	@TopicId	Int = NULL ,
	@TopicTypeId	Int = NULL ,
	@IsSelected	BIT = NULL ,
	@TopicArray	VARCHAR(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
*
*	Date		Author		Description
*	===========	======		===========
*	05-Mar-2004	David Poole	Created
*/	
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
   UPDATE	C
     SET		C.IsSelected = @IsSelected 
    FROM	dbo.Tbl_ContactTopic AS C
    WHERE	C.ContactId = @ContactId
    AND	C.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
      UPDATE	C
        SET		C.IsSelected = @IsSelected 
       FROM	dbo.Tbl_ContactTopic AS c
         INNER JOIN dbo.Tbl_Topic AS T
           ON C.TopicId = T.TopicId
       WHERE	C.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
        SET		C.IsSelected = @IsSelected 
       FROM	dbo.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
       WHERE	C.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
      UPDATE	C
        SET  C.IsSelected = @IsSelected 
       FROM dbo.Tbl_ContactTopic AS c
         INNER JOIN	dbo.fnSplitter(@TopicArray,',') AS FN
            ON	C.TopicId = FN.ID
       WHERE	C.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 
 FROM	dbo.Tbl_ContactTopic AS c
 WHERE	C.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.

@ContactId @TopicId @TopicTypeId @IsSelected @IsTopicArray Subtree
cost/fail
NULL NULL NULL NULL NULL NA / Fail1
276 NULL NULL NULL NULL NA / Fail1
NULL NULL NULL 1 NULL NA / Fail1
276 NULL NULL 1 NULL 0.0133
276 15 NULL 1 NULL 0.0133
276 NULL NULL 1 '13,14,15' 0.01802
276 NULL 2 1 '13,14,15' 0.0215
276 NULL 2 1 NULL 0.0180

1 Test for the ommission of mandatory arguments.
2 I tested moving the function from an INNER JOIN to the
WHERE C.ContactId = @ContactId AND C.TopicID IN ( SELECT ID FROM dbo.fnSplitter(@TopicArray,','))
It shaved 0.0001 off the subtree cost in this test and had no affect where the @TopicTypeId parameter was specified. For such a small saving I decided to leave the function where it was.
3 I also carried out some additional tests to confirm that the correct query was executed for various other combinations of parameters. For example, if the @TopicId parameter was specified then other selection parameters should be ignored.

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.

Total article views: 9210 | Views in the last 30 days: 1
 
Related Articles
FORUM

Procedure return value problem

Procedure return value problem

FORUM

A Simple procedure but no returning value

A Simple procedure but no returning value

ARTICLE

Contact us

Learn how to contact the staff at SQLServerCentral.com

FORUM

Stored procedure returning wrong value ... -1

Stored procedure returning wrong value ... -1

FORUM

A Simple procedure but no returning value

Please Help A Simple procedure but not returning value

Tags
advanced querying    
miscellaneous    
programming    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones