Problem with sql like

Question

Hi,

I'm writing sql query to my SqlCommand, and some part of them looks like:

WHERE Path like N'+(select top 1 path from  [Paths] where objectcode=@objectCode and objecttype=@objectType)+%' order by path desc,objecttype desc

I get no records , bu when I wrote the same in sql server 2005 i have plenty rows...

where path like (select top 1 path from  [Paths] where objectcode='eg' and objecttype='0')+'%'

order by path desc

What's wrong ??

@objectCode is 'eg'

I saw something:

I forget to add cmd.parameters, and and I don't get error, so I think that sql is seeing this parameters as normal string, and don't get value from there, so:

eg: it get 'objectcode=@objectCode', and not 'objectcode='EG''

Answer 1

SQL will not substitute your @objectcode parameter because it is inside a string literal.

You need to get rid of the string literal, use something more like:

WHERE Path like (select top 1 path from  [Paths] where objectcode=@objectCode and objecttype=@objectType)+'%' order by path desc,objecttype desc

Did you have a reason why you were enclosing the sub-query in speechmarks? Its not necessary.