Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.05 seconds. Powered By: Snitz Forums 2000