Parametrized Dynamic SQL and Wildcards

Question

Post reply

Parametrized Dynamic SQL and Wildcards

LightningFingers

Mr or Mrs. 500

Points: 558
More actions
May 7, 2009 at 12:29 pm

#155675

Let's start with some code.
DECLARE
@sql nvarchar(MAX),
@OP nvarchar(2),
@Name nvarchar(20),
@Value nvarchar(20);
SET @OP = N' LIKE ';
SET @Name = N'MyColumn';
SET @Value = N'%My_Matching_Value%';
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE ([' + @Name + '] ' + @OP + ' ''@p_Value'')';
print @sql;
EXEC sp_executesql @sql,
N'@p_Value nvarchar(320)',
@p_Value = @Value;
The underscores in My_Matching_Value will get seen as wildcards not as escaped underscores. Is there a simple solution here?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Have a look to BOL for LIKE and search for the "ESCAPE" part.

Greets

Flo

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 2

Your variables content must match the predicates conventions.

In this case the conventions for Like.

Did you test this :

SET @Value = N'%My[_]Matching[_]Value%';

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LightningFingers Mr or Mrs. 500 Points: 558 More actions · Answer 3

The @Value is coming from an ASP.NET business object. So should I be doing a search and replace for the _ character and using the ESCAPE thing? Seems like there should be a better way.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

I prefer the ESCAPE because I think it is more clean but it's your choice 😉

Greets

Flo

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

mikesigsworth (5/7/2009)
The @Value is coming from an ASP.NET business object. So should I be doing a search and replace for the _ character and using the ESCAPE thing? Seems like there should be a better way.

If your asp user knows (s)he should follow LIKE conventions, (s)he should use the needed method !

How would you determine if (s)he intends a wildcard or an actual symbol _ ?