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: [email protected]
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: [email protected]
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: [email protected]
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 [email protected]
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm
