SQL Parameter and wildcards, how to make it work?

J_bob_1

Hello, I have what should be a very simple problem, but I cant solve it.

I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)

Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:

DECLARE @Dv_id nchar(15)

SET @Drv_id = '%'

SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id

SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'

OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows.

The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between:

WHERE Drv_id LIKE @Drv_id

and

WHERE Drv_id LIKE '%'

The wildcard statement '%' is supposed match everything, correct

It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.

Is there any way around this

J_bob_1

Grrr...

Well, ive fixed it. and I must say that if the search function on this website worked the first time I was here, I never would have posted this thread to begin with.

All I had to do to get this working was to set the DECLARE type to nvarchar instead of nchar. Why this makes it work, or why it didnt work with nchar to begin with, I may never know.

So, here is the critical change in my case:

DECLARE @Dv_id nvarchar(15)