May 1, 2005 at 10:29 pm
Syntax error converting the varchar value 'SELECT coid FROM Nov_BusinessProfile BProfile WHERE (BProfile.BP_BusinessNature LIKE '%1,[ ]%') OR ( BProfile.BP_BusinessNature LIKE ',%[ ]1%') OR ( BProfile.BP_BusinessNature LIKE '%,[ ]1%') OR ( BProfile.BP_BusinessNature LIKE '%,[ ],1%') OR ( BProfile.BP_BusinessNature LIKE '1') OR ( BProfile.BP_BusinessNature LIKE '[ ]%,1,[ ]%')' to a column of data type
The above error is coming , when i using this statement in a stored Procedure used for searching .
May 2, 2005 at 1:37 am
'SELECT coid FROM Nov_BusinessProfile BProfile WHERE (BProfile.BP_BusinessNature LIKE ''%1,[ ]%'')'
Note the double quotes. You need to double quote all your LIKE criterias. This should do it assuming you're executing this dynamically.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 3, 2005 at 4:44 am
Looking at your SQL it appears you are tring to find BP_BusinessNature cols with a value 1 in them where the field holds a comma separated list of values - your SQL could have problems if e.g. in CSV field you have values of 10,11,12 etc as these will match some of your values
Possibly safer is:
WHERE ',' + Replace(IsNull(BProfile.BP_BusinessNature,''),' ','') + ',' LIKE '%,1,%'
This is by no means efficient (you should really normalise design to hold all business nature entries in linked table) but what this does is
IsNull = make sure we treat Null records as empty string
Replace = Change spaces into empty string to make sure we have no spaces
',' + ... + ',' = tack a comma to fron and end so e.g. 1,2,3 = ,1,2,3, so where ever 1 appears in list it will have commas on either side
James Horsley
Workflow Consulting Limited
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply