Show us your code as it stands when it puked that error.
-----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 14:56 To: SQL Subject: RE: Concatenation assistance... I am getting this error now: Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 153 Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure. BTW I did NOT use the NOT IN , i just tested with IN first. Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 1:58 PM To: SQL Subject: RE: Concatenation assistance... It shouldn't return records with IDs in the list, should it? Weren't we using a NOT IN() condition? Take the NOT out if you want it the other way around. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 13:57 To: SQL Subject: RE: Concatenation assistance... Correction, it worked it just didnt return the records with ID's inthe list. I have been messing with it, but still cant get it correct. Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 1:48 PM To: SQL Subject: RE: Concatenation assistance... 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:> Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
