| Author |
 Topic  |
|
|
digory
Starting Member
Australia
13 Posts |
 Posted - 03/23/2002 : 17:54:17
|
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
|
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 |
 |
|
| |
Topic |
|
|
|
FUNCTION: ArrayToTable
