yep, in-effecient... -----Original Message----- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 15 January 2003 11:42 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] SQL question
> Also, T-SQL string functions are very efficient I assume that was a typo and you mean inefficient. > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED]] > Sent: 15 January 2003 11:40 > To: '[EMAIL PROTECTED]' > Subject: RE: [ cf-dev ] SQL question > > > 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] > -- ** 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]
