Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance this
>>sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
>> My example stored procedure and plain sql are totally equivilent - one
>> works the other does not.
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Dan,
>> you are not making sense to me. % means "any string of zero or more
>> characters". WHERE branch LIKE '%%' is equivilent to not having the
>> WHERE clause at all. % is not a "wild card character." My example
>> stored procedure and plain sql are totally equivilent - one works the
>> other does not.
>> Gary
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>>
>