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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 passing a csv with a twist

Author  Topic 

liorfr1
Starting Member

1 Post

Posted - 2004-06-22 : 14:15:39
hi all
I'm passing a csv of a 100 or so id's to a stored procedure and my goal is to update those records.
Due to matters of efficiancy i do not want to use the 'IN' method of doing so (like where id in (id1,id2...,id100)).
I know there is another efficiant way.
common guys, help me.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-22 : 14:25:20
put them in a table and join to it.

- Jeff
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-22 : 15:18:54
what 'efficiancy' reasons do you not like 'IN'. Because of dynamic sql?

you can do:
from myTable
where (',' + @csvList + ',') like '%,' + id + ',%'

EDIT: id probably needs a convert: convert(nvarchar,id)

Corey
Go to Top of Page

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2004-06-23 : 06:54:51
You could do something like this to parse the csv list and put it in a temp table so you can join on it later.


CREATE TABLE #Tmp_ID(
ID INT NOT NULL)

WHILE @SYSID > ''
BEGIN
IF (SELECT PATINDEX('%,%',@SYSID)) > 0--check for a comma
BEGIN
--get the first part
INSERT INTO #Tmp_FacID
SELECT SUBSTRING(@SYSID,1,PATINDEX('%,%',@SYSID)-1)

--reset the string
SELECT @SYSID = SUBSTRING(@SYSID,PATINDEX('%,%',@SYSID)+1,LEN(@SYSID))
END
--now insert final val
ELSE
BEGIN
INSERT INTO #Tmp_FacID
SELECT @SYSID

--reset str
Select @SYSID = ''
END
END


HTH,
Scooter McFly
Go to Top of Page
   

- Advertisement -