SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 FUNCTION: ArrayToTable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

digory
Starting Member

Australia
13 Posts

Posted - 03/23/2002 :  17:54:17  Show Profile  Visit digory's Homepage  Reply with Quote

Hi this is my first post in these forums, so, I apoligize if this function has been submitted before. It's a function that allows you to send a regular array to SQLServer and have it treated as a TABLE type.

The function is cool because it allows you to throw an array directly from ASP (or whatever) directly into a sproc. This is useful, especially if you have a heap-O-checkboxes or radio's on your page.

For example, if you had 20 checkboxes named "foo" and each one had a different .value, you could retrive the "checked" values as an array by doing this:

---------------------- PSUEDO CODE ----------------------

checkedBoxes = Request.Form( "foo" )

-- Then use that array to get details about each foo, like so:

sql = "SELECT [details] " _
& "FROM fooDetails " _
& "WHERE fooID IN " _
& "SELECT Fld FROM dbo.ArrayToTable( " & checkedBoxes & " )"

---------------------------------------------------------

Here's the function

---------------------- ArrayToTable.sql -----------------

CREATE FUNCTION ArrayToTable ( @array VarChar( 4000 ), @delimiter VarChar( 6 ) )

/*
Name: ArrayToTable
Return Values: TABLE ( ( VarChar ) Fld )
Description: Receives a delimited list of values and returns them as
a single column table of varchar values
*/


RETURNS @tblReturn TABLE (
Fld VarChar( 500 )
) AS
BEGIN

-- track where in the text we are...
DECLARE @startPos Int
DECLARE @endPos Int

-- each element that we find in the text
DECLARE @elementValue VarChar( 500 )

SET @array = @array + @delimiter
SET @startPos = 0
SET @endPos = CHARINDEX( @delimiter, @array, @startPos )


-- loop until we reach the end of the text
WHILE ( ( @endPos > 0 ) And ( @startPos LEN( @array ) ) )
BEGIN

SET @elementValue = CONVERT( VarChar( 500 ), LTRIM( RTRIM( SUBSTRING( @array, @startPos, @endPos - @startPos ) ) ) )

-- did we find a valid element?
IF( LEN( @elementValue ) > 0 )
INSERT INTO @tblReturn ( [Fld] ) VALUES ( @elementValue )

-- move the pointers
SET @startPos = @endPos + LEN( @delimiter )
SET @endPos = CHARINDEX( @delimiter, @array, @startPos )

END

RETURN

---------------------------------------------------------






Edited by - digory on 04/10/2002 14:59:12

digory
Starting Member

Australia
13 Posts

Posted - 04/10/2002 :  14:28:24  Show Profile  Visit digory's Homepage  Reply with Quote
Errr... actually, better sample code for consuming that function would be:


---------------------- PSUEDO CODE ----------------------

checkedBoxes = Request.Form( "foo" )

-- Then use that array to get details about each foo, like so:

sql = "SELECT [details] " _
& "FROM fooDetails " _
& "WHERE fooID IN " _
& "SELECT Fld FROM dbo.ArrayToTable( " & checkedBoxes & ", ',' )"

---------------------------------------------------------


... as you can see, in the initial psuedo-code sample, I forgot to pass the 2nd parameter; the delimiter, which in this case is a comma.

Now, let's imagine that you had a different delimiter - the | character for instance - you would then call the function like so:


---------------------- PSUEDO CODE ----------------------

qID = "1|2|3|48|999|1026"

-- Then use that array to get details about each question, like so:

sql = "SELECT [id], [description] " _
& "FROM questions " _
& "WHERE [id] IN " _
& "SELECT Fld FROM dbo.ArrayToTable( " & qID & ", '|' )"

---------------------------------------------------------


Edited by - digory on 04/10/2002 14:32:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000