« June 2004 | Main | August 2004 »

July 25, 2004

Passing a variable number of parameters to a stored procedure

I came up against this problem any times. Say you want to retrieve the details of a few rows from a table by passing each of those variables into a stored procedure.

Traditionally you would have to have an array of ID's (or whatever you are basing your SELECT on) and you would loop through each value in your code, calling the stored procedure each time with all the rigmarole of opening a connection, reading the data, closing the connection, doing stuff with the data, then going back through the loop again.

Its a bit monolithic. No finesse.

It would be much better if you could just pass the stored procedure a list of ID's and have it return a recordset as normal.

Lets have an example table first:

Table name: AddressBook
ID	Name		Telephone	Email
1	John Boy	123456		jb@mail.com
2	Micky		98792837	mc@home.nl
:	:		:		:
etc....

you get the idea

Here's how I managed it:

CREATE PROCEDURE DatabaseName.ProcName
	@Ids	VARCHAR(500);
AS
	CREATE TABLE #IDs ( ID INT );
	DECLARE @Comma 		INT;
	DECLARE @ID	INT;
BEGIN


	/* now process the @Ids variable */

	SELECT @Comma = CHARINDEX( ',' , @Ids , 0 );

	IF @Comma = 0 BEGIN

		SELECT @ID = (@Ids);

	END

	IF @Comma <> 0 BEGIN

		SELECT @ID = LEFT( @Ids, @Comma - 1 );

		SELECT @Ids = RIGHT( @Ids, LEN(@Ids) - @Comma );

	END

		

	WHILE LEN(@Ids) > 0 BEGIN

		

		INSERT INTO #ConfigsID (@ID) VALUES (ConfigID);

		

		/* now process the @Ids variable */

		SELECT @Comma = CHARINDEX( ',' , @Ids , 0 );

		IF @Comma = 0 BEGIN

			SELECT @ID = (@Ids);

		END

		IF @Comma <> 0 BEGIN

			SELECT @ID = LEFT( @Ids, @Comma - 1 );

			SELECT @Ids = RIGHT( @Ids, LEN(@Ids) - @Comma );

		END

	END



	SELECT DISTINCT * FROM AddresBook WHERE ID IN (#IDs) ORDER BY Name;

END

GO

But even that was a bit messy - the block that breaks off the next ID is used twice. We can modularise that by creating a user defined function and readjusting the code, so:

CREATE FUNCTION splitString ( @String NVARCHAR(500) )
	RETURNS @Return  TABLE ( ID INT, List NVARCHAR(500))
AS
BEGIN
	DECLARE @Comma 	INT;
	DECLARE @ID INT;
	
	SELECT @Comma = CHARINDEX( ',' , @String , 0 );
	IF @Comma = 0 BEGIN
		SELECT @ID = (@String);
		SELECT @String = (NULL);
	END
	IF @Comma <> 0 BEGIN
		SELECT @ID = LEFT( @String, @Comma - 1 );
		SELECT @String = RIGHT( @String, LEN(@String) - @Comma );
	END
	
	INSERT @Return
		SELECT @ID, @String
	RETURN
END


CREATE FUNCTION splitList ( @List NVARCHAR(500) )

	RETURNS @Return  TABLE ( ID INT )

AS

BEGIN

	DECLARE @ID INT;

	DECLARE @Temp TABLE ( ID INT );

	

	SELECT @ID = ( SELECT ID FROM splitString( @List ) );

	SELECT @List = ( SELECT List FROM splitString( @List ) );



	WHILE LEN(@List) > 0 BEGIN

		INSERT @Temp SELECT ( @ID );

		SELECT @ID = ( SELECT ID FROM splitString( @List ) );

		SELECT @List = ( SELECT List FROM splitString( @List ) );

	END

	INSERT @Temp SELECT ( @ID );

	

	INSERT @Return

		SELECT * FROM @Temp

	RETURN

END

CREATE PROCEDURE Database.FunctionName
	@IDs VARCHAR(500)
AS
	CREATE TABLE #IDs ( ID INT )
BEGIN
	INSERT #IDs SELECT * FROM splitList( @IDs );
	SELECT DISTINCT * FROM AddressBook WHERE ID IN (SELECT * FROM #IDs) ORDER BY Name;
END
GO

Your code is cleaner as you can use your stored procedure like a normal stored procedure and just pass it a comma delimeted list of ID's to retrieve a recordset. Cant say it will do too much for your SQL server efficiency though - I have never had to deal with huge overheads so I've no idea what effect this will have on your performance.

The code side of things should be much more efficient as the connection needs only be made once. The SQL server is less efficient though with all that string parsing. There should be a sweet spot though. I leave it up to better men than I to find out where it is :)

Posted by dottie at 1:47 AM

July 24, 2004

301 redirects, custom 404 and .htaccess

I managed to get the Deer Park Hotel website swapped over to my new version last night. I really need to get and keep good page rank for this site so I have also implemented things I normally dont bother with (*shame!*) - the custom 404 and permanenet redirects, better known as 301 redirect.

My redesign of the Deer Park site was in a sub-directory newsite for long enough that Yahoo and Google managed to index it - mainly due to me posting the URL to various chat boards looking for testing on Mac browsers. I didnt put in a robots.txt file to close off the newsite directory to search engines - my bad.

Anyway, to keep those indexes pointing at my creation I need to create some 301 redirects. Easy enough. I crank up my text editor, open (or create) the .htaccess file and start with:

redirect 301 /newsite/index.html http://www.deerpark-hotel.ie/index.html

This basically tells the server that should someone come looking for the file in the directory /newsite/index.html that it has moved to http://www.deerpark-hotel.ie/index.html. Try it and see. All I had to do then was create a similar entry for each and every page on the site - yawn - I briefly - briefly! - considered including the images. I suppose I could write a Perl script that would create a 301 redirect list for me for all the contents of the newsite directory but I'll do it right next time and include a robots.txt file :)

Something I always wanted to do was to include a custom 404 error page. Not really up there with sky diving and extreme sports I know, but I am a simple man! Again this is easy to do. Fire up your text editor with the .htaccess file again and enter the following line:

ErrorDocument 404 http://www.deerpark-hotel.ie/errorpages/custom404.html

The save it and you're done! Now any time the server returns a 404 error it will display the page above. It is possible to add all sorts of bells and whistles to this to try and suggest a page to the visitor. I might add this when I have time.

The final version (for now...) of the Deer Park Hotel website can be found at:

Deer Park Hotel and Golf Courses, Howth, Dublin, Ireland - set in the grounds of Howth castle with scenic sea views - the perfect spot for your golf vacation

And yes the link above is there to try and gain a little extra pagerank - I'm mercenary, so what!

Posted by dottie at 2:05 PM

July 20, 2004

Labels and the DOM

If you have a label and input as below:

<label for="Stuff">
<input type="text" id="Stuff" name="Stuff" value="xyz" />
<input type="hidden" name="OtherStuff" id="OtherStuff" value="lmno" />
</label>

Then the second input field 'OtherStuff' is not in the DOM - at least in Mozilla. I havent tested IE as I couldnt be bothered.

The solution is to make sure only the input field with the corresponding ID is in the label, so:

<label for="Stuff">
<input type="text" id="Stuff" name="Stuff" value="xyz" />
</label>
<input type="hidden" name="OtherStuff" id="OtherStuff" value="lmno" />

Then it is in the DOM again and you can read and write to it from Javascript - courtesy of Hard-way University :)

Posted by dottie at 2:18 PM

July 16, 2004

FInding the value of a FileUpload form element using Javascript - easy? no. Well... yes!

Struggled with this one for a while tonight. If you want to read the value of a form input element for uploading files, so:

var Temp = document.FormName.FileUploadName.value

then this is wrong

<input name="FileUploadName" type="file">

you have to include value="" even though it is empty. So this is correct:

<input name="FileUploadName" type="file" value="">

Pedantic feckers....

Posted by dottie at 2:38 AM