SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 passing a csv with a twist
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

liorfr1
Starting Member

1 Posts

Posted - 06/22/2004 :  14:15:39  Show Profile
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

USA
7423 Posts

Posted - 06/22/2004 :  14:25:20  Show Profile  Visit jsmith8858's Homepage
put them in a table and join to it.

- Jeff
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/22/2004 :  15:18:54  Show Profile  Visit Seventhnight's Homepage
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

Edited by - Seventhnight on 06/22/2004 15:50:28
Go to Top of Page

scootermcfly
Yak Posting Veteran

USA
66 Posts

Posted - 06/23/2004 :  06:54:51  Show Profile  Click to see scootermcfly's MSN Messenger address
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000