be careful with the creation of temp tables inside a stored procedure, it can sometimes results in the stored procedure recompiling. You should be able to verify this using Profiler.
Also, T-SQL string functions are very efficient (though it may have been upped in SQL2K), so the parsing could take more CPU cycles with large lists. -----Original Message----- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 15 January 2003 11:41 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL question What with everyone's debating on chat, I came up with a similar solution to this... Create a temp table, loop using charindex and place the ints into the table, then do an IN with the WHERE a IN (SELECT col from #temp) Thanks everyone! Paul PS Ben I thought you were working :) > You could use a sql funtion to turn the list into a table and call the > function: > > CREATE FUNCTION dbo.fn_List2Table_int (@List varchar(8000)) > RETURNS @List_tbl table(ID int) AS BEGIN > DECLARE @pos int > SET @List = @List + ',' > SET @pos = CHARINDEX(',', @List) > WHILE @pos > 0 AND @pos IS NOT NULL > BEGIN > INSERT INTO @List_tbl(ID) VALUES > (CONVERT(int,LEFT(@List,@pos - 1))) > SET @List = RIGHT(@List, LEN(@List) - @pos) > SET @pos = CHARINDEX(',', @List) > END > > RETURN > > END > > Then in your SP: > > SELECT * > FROM tbl > WHERE myvar = (SELECT ID FROM fn_List2Table_int(@lstVar)) > > Ben -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
