Using a wildcard with parameter

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

General SQL Server Forums

New to SQL Server Programming

Using a wildcard with parameter

Author

Topic

mburge
Starting Member

3 Posts

Posted - 2010-08-25 : 10:41:57

Hi All,

I have the stored procedure below, which is working fine.

ALTER PROCEDURE [dbo].[spAdminSearch]
@ID Int,
@Name varchar(75)
AS

DECLARE @sql nvarchar(2000),
@parms nvarchar(100)

set @parms = N'@ID varchar(10),
@Name varchar(75)'

SET @SQL = 'Select memberpolicies.policyid,
memberpolicies.StartDate,
memberpolicies.Enddate,
members.firstname,
members.lastname,
members.postalcode,
members.telephone,
policytypes.description
from memberpolicies
inner join members
on
memberpolicies.UserId = members.UserId
inner join policytypes
on
memberpolicies.policytypeid = policytypes.policytypeid
Where 1 = 1'

IF @ID <> ''
SET @SQL = @SQL + ' AND memberpolicies.policyid = @ID'

IF @Name <> ''
SET @SQL = @SQL + ' AND members.lastname LIKE @Name'

EXECUTE sp_executesql @SQL, @Parms,
@ID, @Name

--------

I'm trying to add a wildcard to the @Name parameter. As below:

IF @Name <> ''
SET @SQL = @SQL + ' AND members.lastname LIKE %@Name%'

However this throws an incorrect syntax error. Any ideas?

Thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-25 : 10:48:14

Try ' AND members.lastname LIKE '

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-25 : 10:48:56

oops pushed the wrong button

' AND members.lastname LIKE ' +'''%' + @NAME + '%'''

Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2010-08-25 : 10:54:49

My preference would be:


IF @Name <> ''
BEGIN
    SET @Name = '%' + @Name + '%'
    SET @SQL = @SQL + ' AND members.lastname LIKE @Name'
END

and possibly only add "%" if @Name does not already contain them - i.e. if the user typed

XXX%

they adding NO leading "%" would give them the ability to do a "starts with" query. Ditto an "ends with" query.

Kristen
Test

22859 Posts

Posted - 2010-08-25 : 10:57:48

"' AND members.lastname LIKE ' +'''%' + @NAME + '%'''"

If you want to do it that way I think you need to use:


' AND members.lastname LIKE ''%'' + @Name + ''%'''

to avoid risk of SQL Injection / runtime errors from embedded single-quotes etc.

mburge
Starting Member

3 Posts

Posted - 2010-08-25 : 11:54:33

Thanks for both of your replies, very helpful!

Kristen i went with your first example as it looked the easiest, works great! Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources