Re: SQL IN Statement

[Robert Shaw]

Thanks, this works great. And thanks for the article also.
Robert
----- Original Message ----- 
From: "Daniel Elmore" <[EMAIL PROTECTED]>
To: <list@dfwcfug.org>
Sent: Friday, January 28, 2005 4:27 PM
Subject: RE: SQL IN Statement


Since dynamic SQL runs in it's own memory space, it screws up permissions
and transaction rollback settings (such as using XACT_ABORT). If you want 
to
get the full scoop try reading this:
http://www.sommarskog.se/dynamic_sql.html

Here's the code and some example usage:
CREATE FUNCTION ListToTable (@list ntext)
     RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                         number  int NOT NULL) AS
  BEGIN
     DECLARE @pos      int,
             @textpos  int,
             @chunklen smallint,
             @str      nvarchar(4000),
             @tmpstr   nvarchar(4000),
             @leftover nvarchar(4000)
     SET @textpos = 1
     SET @leftover = ''
     WHILE @textpos <= datalength(@list) / 2
     BEGIN
        SET @chunklen = 4000 - datalength(@leftover) / 2
        SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
@chunklen))
        SET @textpos = @textpos + @chunklen
        SET @pos = charindex(',', @tmpstr)
        WHILE @pos > 0
        BEGIN
           SET @str = substring(@tmpstr, 1, @pos - 1)
           INSERT @tbl (number) VALUES(convert(int, @str))
           SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
           SET @pos = charindex(',', @tmpstr)
        END
        SET @leftover = @tmpstr
     END
     IF ltrim(rtrim(@leftover)) <> ''
        INSERT @tbl (number) VALUES(convert(int, @leftover))
     RETURN
  END
/* example use */
/* pretend this list came from a CF variable (multi-select box for 
example)
*/

declare @ltIDs varchar(100)
set @ltIDs = '1,2,4,6,7,8'
//run this to see the how the data is stored
select * from dbo.ListToTable(@ltIDs)
/* the function ListToTable receives a varchar comma-delimited list 
variable
and returns a table, so rather than store the return table in a temp table
and joining it an insert query, let's call the function directly in the
query */

insert into many_to_many_table (entityID,subtypeID)
select @entityID, A.number
from dbo.ListToTable(@ltIDs) A
-Daniel Elmore

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf 
Of
Robert Shaw
Sent: Friday, January 28, 2005 3:50 PM
To: list@dfwcfug.org
Subject: Re: SQL IN Statement

Thanks, can you send me the code for the UDF. I really appreciate it.
Just curious, other than the fact is not compiled, what is your objection 
to
a dynamic SQL
----- Original Message -----
From: Daniel Elmore
To: list@dfwcfug.org
Sent: Friday, January 28, 2005 1:15 PM
Subject: RE: SQL IN Statement

Robert, to use the char string in an SQL IN clause most people would say
just use dynamic SQL (EXEC ...). However, I would never recommend anyone 
to
use dynamic SQL unless it was an usual circumstance. Instead, I use a UDF
called ListToTable which converts a varchar list to a table variable (not 
a
temp table!) with rows. Therefore you can write code like so:

DECLARE @myIDString varchar(100)
SET @myIDString = '2,3,4,5,6,7,8'
SELECT *
FROM entCustomers
WHERE CustID IN (select ID from dbo.ListToTable(@myIDString) )
Does that make sense? If you want the code for the UDF let me know.
Daniel Elmore

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf 
Of
Robert Shaw
Sent: Friday, January 28, 2005 10:25 AM
To: list@dfwcfug.org
Subject: SQL IN Statement

I have a cookie that hold multiple ID numbers for specific jobs. I want to
pass that through a storedProcedure to the server to use as an IN clause. 
I
have to pass the string as text, but the value on the SQL server site has 
to
be numeric.

I can write the entire query string and pass it to SQL server but is there 
a
way to just pass the values needed for the IN clause.

What am I missing?.


Robert Shaw
Edata Alliance, Inc.
www.EdataAlliance.com
DataOnTheWeb
www.DataOntheWeb.com
972-463-3515
972-463-9215
972-463-9896
----------------------------------------------------------
To post, send email to list@dfwcfug.org
To unsubscribe:
  http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
  http://www.dfwcfug.org/form_MemberRegistration.cfm


----------------------------------------------------------
To post, send email to list@dfwcfug.org
To unsubscribe: 
  http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe: 
  http://www.dfwcfug.org/form_MemberRegistration.cfm