LIKE Operator doesn´t return records

> SET @strSQLWHERE = @strSQLWHERE + ' AND dbo.tbl_Kd.txtKdNr LIKE
> @strKdNr '

Uh, are you sure it's not supposed to be:

+ ' AND fbo.tbl_Kd.txtKdNr LIKE ''' + @strKdNr + '''';'

?

A

On May 14, 2:30 pm, Karl Fuchs wrote:
> Hello,
>
> I use this WHERE-Clause in a SQL-Statement to get records from a
> Table:
>
> @strKdNr nvarchar(14) = Null, --Parameter
> ...
> If @strKdNr IS NOT NULL --WHERE-Clause
> BEGIN
> SET @strSQLWHERE = @strSQLWHERE + ' AND dbo.tbl_Kd.txtKdNr LIKE
> @strKdNr '
> END
> ..
> Datatype of the field searched in is nvarchhar, length is 14, Format
> like 99999AB1234567
>
> The Problem is: If I search for 97888AB00002% I get the records
> needed. But if I search for 97888AB000020% or 97888AB0000201 no
> records are found, although they are shown If I search for
> 97888AB00002%. What can be wrong here?
>
> Thank you in advance
>
> Karl

Besides your immediate problem, your code is not immune from SQL
injection. Use dynamic SQL with parameters.

Karl Fuchs (fuka@ivsn.com) writes:
> I use this WHERE-Clause in a SQL-Statement to get records from a
> Table:
>
> @strKdNr nvarchar(14) = Null, --Parameter
> ...
> If @strKdNr IS NOT NULL --WHERE-Clause
> BEGIN
> SET @strSQLWHERE = @strSQLWHERE + ' AND dbo.tbl_Kd.txtKdNr LIKE
> @strKdNr '
> END
> ..
> Datatype of the field searched in is nvarchhar, length is 14, Format
> like 99999AB1234567
>
> The Problem is: If I search for 97888AB00002% I get the records
> needed. But if I search for 97888AB000020% or 97888AB0000201 no
> records are found, although they are shown If I search for
> 97888AB00002%. What can be wrong here?

We don't see the full code. Particularly, we don't see your call to
sp_executesql and how the second parameter looks like. I would double
check so that you don't by mistake say nvarchar(13) there.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hello Erland,

On 15 Mai, 00:37, Erland Sommarskog wrote:
>
> > The Problem is: If I search for 97888AB00002% I get the records
> > needed. But if I search for 97888AB000020% or 97888AB0000201 no
> > records are found, although they are shown If I search for
> > 97888AB00002%. What can be wrong here?
>
> We don't see the full code. Particularly, we don't see your call to
> sp_executesql and how the second parameter looks like. I would double
> check so that you don't by mistake say nvarchar(13) there.
>
Sometimes you don't see the forest because of all the trees ... your
assumption was right ...

In the sp_executesql-part of the procedure the parameter was written
with length 13 ...

Thank you for helping.

Karl