It should work. Maybe I missed something syntax-wise, but the theory is still good.
The URL you mention correctly states there can be issues with permissions. And as far as performance goes... that's debatable, depending on your frequency of use, number of items in the list, etc. You have to weigh that. We use that sort of dynamic SQL approach all over the place, and it works great. You say it doesn't work for you... why not? What was the error? -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 13:28 To: SQL Subject: RE: Concatenation assistance... Negative, that does NOT work...I did a web search and found this article, BUT GOOD GOD is all I have to say about that! http://www.algonet.se/~sommar/arrays-in-sql.html The first paragraph asks the same question i did....the work around is MASSIVE! Thanks Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 12:31 PM To: SQL Subject: RE: Concatenation assistance... I think it's confused about what to do with that list. You can't compare text values in an IN() clause like you do with = clauses. You have to compile an SQL string, and then execute it. Something like this: Declare @SQL varchar(8000) Set @SQL = 'select blah blah from blah where x.cyb_record_num NOT IN (' + @spExludeListB + ')' execute (@SQL) That should do it. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 12:18 To: SQL Subject: RE: Concatenation assistance... I finally figured that out, thanks. Here is a second question, I have a SP that I pass into it a list e.g. '32','432','43','23','112' then I use that parameter for the WHERE clause, using IN e.g. (CAST(x.cyb_record_num AS varchar(25)) NOT IN (@spExcludeListB)) However it still returns the records with ID's in the list. If i hard code it, works as it should: (x.cyb_record_num NOT IN ('32','432','43','23','112')) WTF??? Anybody have any pointers? I have been banging my head for an hour. Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 9:31 AM To: SQL Subject: RE: Concatenation assistance... Did you initialize your @NewRecordIDList = '' first? If not, then it's null, and null + varchar = null. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 06:10 To: SQL Subject: RE: Concatenation assistance... Correction...it DOES display the @SID for the PRINT statement, however after the END I have this: PRINT 'TEST' PRINT 'HELLO' + LTRIM(@NewRecordIDList) This displays the following: TEST That is it, why doesnt the @NewRecordIDList get displayed? Brad -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 6:07 AM To: SQL Subject: Concatenation assistance... Why doesn't this output anything? I know that the @SID has a AT least 1 value??? ... WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @SID -- appendIDTo @NewRecordIDList SET @NewRecordIDList = @NewRecordIDList + ',' + CAST(@SID AS VARCHAR(25)) END ... Thanks Brad [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
