Wild Cards in stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Wild Cards in stored procedures

Author

Topic

sling-it
Starting Member

2 Posts

Posted - 2012-10-28 : 01:25:18

I'm trying to create a stored procedure for looking up members with only partial information

query like:
select memberID, memberLname, memberFname
from member
where memberLname like 'Mic%' or memberFname like 'Jo%'

BUT When you apply this to a stored procedure, it won't work.

I've tried the following in the procedure:

select.............
where memberLname like @Lname + '%' or memberFname like @Fname + '%'

then I tried it with () arround the concatinated variables

I've tried

select .............
where memberLname like @Lname or memberFname like @fname
--then ran: exec Myprocedure 'Mic%', 'Jo%'

Neither work.
So does anyone have any ideas? It's a really simple procedure/query, but absolutely won't work

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-28 : 11:47:05

select.............
where memberLname like @Lname + '%' or memberFname like @Fname + '%'

will work.
Maybe you have the variables defined as char instead of varchar or have trailing spaces

select.............
where memberLname like rtrim(@Lname) + '%' or memberFname like rtrim(@Fname) + '%'

If that doesn't work look for non-ascii characters

>> but absolutely won't work
The concept ok - it's probably a data issue.
Also check tht what is passed to the SP is what you expect

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sling-it
Starting Member

2 Posts

Posted - 2012-10-28 : 12:29:39

Now that would answer my question! Ok, I've got some changes to make. Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources