Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
digory
Starting Member
13 Posts |
 Posted - 2002-03-23 : 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 pointersSET @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
13 Posts |
Posted - 2002-04-10 : 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 |
 |
|
|
|
|
|
|
|
FUNCTION: ArrayToTable