using LIKE and ESCAPE in stored procedures

13 May 2005 11:08 AM

meedax

Using the following stored procedure it doesn't appear that the & operator is
being escaped.

When supplying @Category as 'Monitors \& Projectors' the stored procedure
returns 0 rows.

However if I use the select statement directly in query analyzer it
correctly returns rows.

Am I trying to escape this character in the wrong way?

CREATE PROCEDURE sp_GetSubCategory

@Category varchar(20)

AS

SELECT *
FROM tblSubCategories
WHERE Category like @Category
ESCAPE '\'
GO

13 May 2005 11:32 AM

Jacco Schalkwijk

'Monitors \& Projectors' is 22 characters long, longer than the maximum
length of the parameter of the stored procedure.

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"meedax" <mee***@discussions.microsoft.com> wrote in message
news:EC77FC6C-96FD-480D-9AF9-58AB034AF203@microsoft.com...
> Using the following stored procedure it doesn't appear that the & operator
> is
> being escaped.
>
> When supplying @Category as 'Monitors \& Projectors' the stored procedure
> returns 0 rows.
>
> However if I use the select statement directly in query analyzer it
> correctly returns rows.
>
> Am I trying to escape this character in the wrong way?
>
>
> CREATE PROCEDURE sp_GetSubCategory
>
>
> @Category varchar(20)
>
> AS
>
> SELECT *
> FROM tblSubCategories
> WHERE Category like @Category
> ESCAPE '\'
> GO
>

13 May 2005 12:49 PM

meedax

School boy error :s

Thannks jacco

Show quote

"Jacco Schalkwijk" wrote:

> 'Monitors \& Projectors' is 22 characters long, longer than the maximum
> length of the parameter of the stored procedure.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "meedax" <mee***@discussions.microsoft.com> wrote in message
> news:EC77FC6C-96FD-480D-9AF9-58AB034AF203@microsoft.com...
> > Using the following stored procedure it doesn't appear that the & operator
> > is
> > being escaped.
> >
> > When supplying @Category as 'Monitors \& Projectors' the stored procedure
> > returns 0 rows.
> >
> > However if I use the select statement directly in query analyzer it
> > correctly returns rows.
> >
> > Am I trying to escape this character in the wrong way?
> >
> >
> > CREATE PROCEDURE sp_GetSubCategory
> >
> >
> > @Category varchar(20)
> >
> > AS
> >
> > SELECT *
> > FROM tblSubCategories
> > WHERE Category like @Category
> > ESCAPE '\'
> > GO
> >
>
>
>