How do I use Wildcards %% with dynamic SQL?

Good afternoon experts!

I need to use wild cards on the following Stored Procedure, on variable @strrequiredoptions. The column family may have several values for the same record. My sample shows how I do it for standard SQL. This does not return an error, but I get nothing returned, even if there is only 1 vale in the Family column.

CREATE PROCEDURE spGetReqOptionDetails
 (
    @strrequiredoptions nvarchar(250)
)
  As

declare @SQL VARCHAR(8000)
declare @temp nvarchar(250)
 set @temp = '%' + @strrequiredoptions + '%'
SET @SQL =  'select  *  from CMRC_Products WHERE family in (''' + Replace(@temp ,',',''',''') + ''') '

PRINT @SQL

EXEC (@SQL)
GO
dplsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SjoerdVerweijCommented:
CREATE PROCEDURE spGetReqOptionDetails
 (
    @strrequiredoptions nvarchar(250)
)
  As

declare @SQL VARCHAR(8000)
declare @temp nvarchar(250)
 set @temp = '%' + @strrequiredoptions + '%'
SET @SQL =  'select  *  from CMRC_Products WHERE family like ''' + Replace(@temp ,',',''',''') + ''' '

PRINT @SQL

EXEC (@SQL)
GO
0
acampomaCommented:
I don't think you can use % with in
you have to use it with like
0
cjm30305Commented:
Your PRINT @SQL Should have returned something, so you are concating a null onto your string.  Use:

set CONCAT_NULL_YEILDS_NULL off

create procedure .......
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

acampomaCommented:
Or...

declare @strrequiredoptions nvarchar(250)
set @strrequiredoptions='ny'
declare @SQL VARCHAR(8000)
declare @temp nvarchar(250)
 set @temp = '' + @strrequiredoptions + ''
SET @SQL =  'select  *  from lu_Organization WHERE charindex (''' + Replace(@temp ,',',''',''') + ''',Organization_Desc )<>0 '

PRINT @SQL

EXEC (@SQL)
GO
0
dplsrAuthor Commented:


Ok, the problem with Like is that @strrequiredoptions is a list. In my test it is: Showerhead,Handshower
When I use Like, I get an error near(,)

The list must be the prob

How do I get  % % around each item in the list?

%Showerhead%,%Handshower%
0
SjoerdVerweijCommented:
acampoma's should work.
0
SjoerdVerweijCommented:
Wait, no it will not. You'll have to transform to

... Like '%ShowerHead%' And ... Like '%BlockHead%' And ...
0
dplsrAuthor Commented:
That's beyond my expertise, for sure! can you give an example?
0
SjoerdVerweijCommented:
Create Procedure spGetReqOptionDetails
  @StrRequiredOptions nvarchar(250)
As

Declare
  @SQL Character Varying(8000),
  @P Integer

Set @SQL = 'Select * From CMRC_Products Where Family '

Set @P = 1
While (@P > 0)
  Begin
    Set @P = CharIndex(',', @StrRequiredOptions)
    If (@P > 0)
      Begin
        Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' And '
        Set @StrRequiredOptions = SubString(@StrRequiredOptions, @P + 1, 255)
      End
  End

Set @SQL = @SQL + 'Like ''%' + @StrRequiredOptions + '%'''

Print @SQL

Execute (@SQL)

0
dplsrAuthor Commented:
Thanks SjoerdVerweij !

I am getting an error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Like'.
0
SjoerdVerweijCommented:
Ok... could you run it in Query Analyzer and post what @SQL contains?
0
dplsrAuthor Commented:
Select * From CMRC_Products Where Family Like '%Select * From CMRC_Products Where Family In '%Showerhead%' And Like '%Handshower%'%'
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Showerhead'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Handshower'.
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
DECLARE @RC int
DECLARE @StrRequiredOptions nvarchar(250)
SELECT @StrRequiredOptions = N'Select * From CMRC_Products Where Family In ''%Showerhead%'' And Like ''%Handshower%'''
EXEC @RC = [PLF].[dbo].[spGetReqOptionDetails] @StrRequiredOptions
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: PLF.dbo.spGetReqOptionDetails'
SELECT @PrnLine = '      Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
0
dplsrAuthor Commented:
i change on like ti in sorry i will resend
0
dplsrAuthor Commented:
Select * From CMRC_Products Where Family Like '%Showerhead%' And Like '%Handshower%'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Like'.
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
DECLARE @RC int
DECLARE @StrRequiredOptions nvarchar(250)
SELECT @StrRequiredOptions = N'Showerhead,Handshower'
EXEC @RC = [PLF].[dbo].[spGetReqOptionDetails] @StrRequiredOptions
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: PLF.dbo.spGetReqOptionDetails'
SELECT @PrnLine = '      Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
0
dplsrAuthor Commented:
I appalogize again! I was trying to fix it myself
0
SjoerdVerweijCommented:
Ah. Note the extra Family:

Create Procedure spGetReqOptionDetails
  @StrRequiredOptions nvarchar(250)
As

Declare
  @SQL Character Varying(8000),
  @P Integer

Set @SQL = 'Select * From CMRC_Products Where Family '

Set @P = 1
While (@P > 0)
  Begin
    Set @P = CharIndex(',', @StrRequiredOptions)
    If (@P > 0)
      Begin
        Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' And Family '
        Set @StrRequiredOptions = SubString(@StrRequiredOptions, @P + 1, 255)
      End
  End

Set @SQL = @SQL + 'Like ''%' + @StrRequiredOptions + '%'''

Print @SQL

Execute (@SQL)
0
cheeky-monkeyCommented:
On the list issue.....if you have a comma delimited string (or delimited by any other character) that you would like to include in a where clause (or even insert, etc.) this is a cool way of achieving it - http://www.sqlteam.com/item.asp?ItemID=5857.
0
PePiCommented:
You may also want to check your sql statement. I think it should be OR and not AND.

HTH!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dplsrAuthor Commented:

Sorry I got called to a meeting!

SjoerdVerweij ,
Your code does not return an error , but it does not return reults either. It should have  returned about 9 records.  sure looks like it should work though

Select * From CMRC_Products Where Family Like '%Showerhead%' And Family Like '%Handshower%'

(0 row(s) affected)

Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
Got it!  
I used PePi  suggestion an used or

    Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' or Family '

Seems to work perfect!

Thank you all!
0
dplsrAuthor Commented:
Good morning all!

I will be glad to make a new question of this if u wish just let me know!

Woops! I now need to add another column and variable into this sp

I have been trying to get the column mfg to have the value of strmfg all morning with no luck. with the config I have now (the sp code is below) I get the error: System.Data.SqlClient.SqlException: Must declare the variable '@temp'.

in the SQL Analyzer when I use DeltaFaucet for strmfg I get:

Select * From CMRC_Products Where mfg like @temp and Family Like '%Showerhead%' or Family Like '%Handshower%'
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@temp'.
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0

CREATE Procedure spGetReqOptionDetails
  @StrRequiredOptions nvarchar(250),
@strmfg nvarchar(50)
As

Declare
  @SQL Character Varying(8000),
  @P Integer,
  @Temp nvarchar(50)
 SET @Temp = @strmfg
Set @SQL = 'Select * From CMRC_Products Where mfg like @temp and Family '

Set @P = 1
While (@P > 0)
  Begin
    Set @P = CharIndex(',', @StrRequiredOptions)
    If (@P > 0)
      Begin
        Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' or Family '
        Set @StrRequiredOptions = SubString(@StrRequiredOptions, @P + 1, 255)
      End
  End

Set @SQL = @SQL + 'Like ''%' + @StrRequiredOptions + '%'''

Print @SQL

0
SjoerdVerweijCommented:
CREATE Procedure spGetReqOptionDetails
  @StrRequiredOptions nvarchar(250),
@strmfg nvarchar(50)
As

Declare
  @SQL Character Varying(8000),
  @P Integer

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''%' + @StrMfg + '%'' And Family '

Set @P = 1
While (@P > 0)
  Begin
    Set @P = CharIndex(',', @StrRequiredOptions)
    If (@P > 0)
      Begin
        Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' or Family '
        Set @StrRequiredOptions = SubString(@StrRequiredOptions, @P + 1, 255)
      End
  End

Set @SQL = @SQL + 'Like ''%' + @StrRequiredOptions + '%'''

Print @SQL
0
dplsrAuthor Commented:
Goodmorning SjoerdVerweij!

Thanks for the additional help!

This is not returning an error now, but it is not selecting by @strMFG either. It just seems to be disregarding it?

0
dplsrAuthor Commented:
more info

it should have returned @StrRequiredOptions  just for deltafaucet but it is returning for all manufactures (mfg)
sql analyzer
DECLARE @RC int
DECLARE @StrRequiredOptions nvarchar(250)
DECLARE @strmfg nvarchar(50)
SELECT @StrRequiredOptions = N'Showerhead,Handshower'
SELECT @strmfg = N'DeltaFaucet'
EXEC @RC = [PLF].[dbo].[spGetReqOptionDetails] @StrRequiredOptions, @strmfg
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: PLF.dbo.spGetReqOptionDetails'
SELECT @PrnLine = '      Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
0
SjoerdVerweijCommented:
Ah, sorry:

CREATE Procedure spGetReqOptionDetails
  @StrRequiredOptions nvarchar(250),
@strmfg nvarchar(50)
As

Declare
  @SQL Character Varying(8000),
  @P Integer

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''%' + @StrMfg + '%'' And (Family '

Set @P = 1
While (@P > 0)
  Begin
    Set @P = CharIndex(',', @StrRequiredOptions)
    If (@P > 0)
      Begin
        Set @SQL = @SQL + 'Like ''%' + Left(@StrRequiredOptions, @P - 1) + '%'' Or Family '
        Set @StrRequiredOptions = SubString(@StrRequiredOptions, @P + 1, 255)
      End
  End

Set @SQL = @SQL + 'Like ''%' + @StrRequiredOptions + '%'')'

Print @SQL
0
dplsrAuthor Commented:

sorry! now it returns nothing
0
dplsrAuthor Commented:
looks like it should work

Select * From CMRC_Products Where Mfg Like '%DeltaFaucet%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
could it be the wildcards? There is only 1 value in mfg for each record. I tried removing them but I always end up with @strmfg in the analyzer instead of DeltaFaucet
0
SjoerdVerweijCommented:
Are you sure some records for this exist? Did you try cutting, pasting and running it in Query Analyzer?
0
dplsrAuthor Commented:
yes, if i take the mfg parts out it returns the strreqoptions for all mfg including deltafaucet. there are several
0
dplsrAuthor Commented:


according to the anaylzer there are none, but there really 12 of them. because if I take out the select for mfg they show up along with Kohler's and everybody else

Select * From CMRC_Products Where Mfg Like '%deltafaucet%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
SjoerdVerweijCommented:
So this returns none...

Select * From CMRC_Products Where Mfg Like '%deltafaucet%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')

But this returns 12?

Select * From CMRC_Products Where (Family Like '%Showerhead%' Or Family Like '%Handshower%')

Could you dump the Mfg fields here? Has to be a typo or something...
0
dplsrAuthor Commented:
yes
0
dplsrAuthor Commented:


You want me to dump mfg fields? I don't understand
0
SjoerdVerweijCommented:
Really, the only way they wouldn't show up is if deltafaucet is spelled differently, e.g. "delta faucet".
0
dplsrAuthor Commented:
I agree 100%, but I have checked all spellings etc. I am copying and pasting right out of the databae into SQL Analyzer. strange. got me!

I have tried it with all mfgs too
0
dplsrAuthor Commented:
here is what sql analyzer shows. but like I say when I take out the mfg code it returns 12 records. I have checked those 12 from one end to the other and they are fine.

DECLARE @RC int
DECLARE @StrRequiredOptions nvarchar(250)
DECLARE @strmfg nvarchar(50)
SELECT @StrRequiredOptions = N'Showerhead,Handshower'
SELECT @strmfg = N'DeltaFaucet'
EXEC @RC = [PLF].[dbo].[spGetReqOptionDetails] @StrRequiredOptions, @strmfg
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: PLF.dbo.spGetReqOptionDetails'
SELECT @PrnLine = '      Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

Select * From CMRC_Products Where Mfg Like DeltaFaucet And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
i took of the %% just to try
0
SjoerdVerweijCommented:
You still have to keep the ' ' when you remove the %s.
0
dplsrAuthor Commented:
ok, I ahave this now. Do you think I should do another question on this? Its got me stumped.

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''' + @StrMfg + ''' And (Family '

results in analyzer


Select * From CMRC_Products Where Mfg Like 'DeltaFaucet' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
SjoerdVerweijCommented:
Weird, weird, weird.

What does this return?

Select * From CMRC_Products Where Mfg Like 'D%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
0
dplsrAuthor Commented:
Ok, I assumed you meant In the analyzer. D entered for @StrMfg returned O too.

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''' + @StrMfg + '%'' And (Family '


Select * From CMRC_Products Where Mfg Like 'D%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:
instead of using the varaiable strmfg in the SP, what would be the syntax for putting "DeltaFaucet" directly into the code. Just for the heck of it.
0
SjoerdVerweijCommented:
Try

Set @SQL = 'Select * From CMRC_Products Where Mfg = 'DeltaFaucet' And (Family '

and

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''D%'' And (Family '
0
dplsrAuthor Commented:
Set @SQL = 'Select * From CMRC_Products Where Mfg Like 'DeltaFaucet' And (Family '

gives  incorrect syntax

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''D%'' And (Family '
returned 0 - crazy

Select * From CMRC_Products Where Mfg Like 'D%' And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
dplsrAuthor Commented:


I am going to test this with some other parameter like in or out of stock.
0
SjoerdVerweijCommented:
Does

Select * From CMRC_Products Where Mfg Like 'D%'

return anything?
0
dplsrAuthor Commented:


I made a bit column and assigned the value 0 to one of the options that should display.

i think this is correct?
Set @SQL = 'Select * From CMRC_Products Where Display = 0  And (Family '

should have returned 1 record  but did not

Select * From CMRC_Products Where Display = 0  And (Family Like '%Showerhead%' Or Family Like '%Handshower%')
Stored Procedure: PLF.dbo.spGetReqOptionDetails
      Return Code = 0
0
SjoerdVerweijCommented:
Does this, in Query Analyzer, return rows?

Select * From CMRC_Products Where Display = 0

If so, did you comment out the Execute line or something?
0
dplsrAuthor Commented:
no
 returned nothing
0
SjoerdVerweijCommented:
Than that's your problem -- if that doesn't return anything, neither will the dynamic SQL.
0
dplsrAuthor Commented:

i used it in dynamic sql

Set @SQL = 'Select * From CMRC_Products Where Display = 0  And (Family '


0
dplsrAuthor Commented:
i will just do a regular query
0
dplsrAuthor Commented:


I tryed this in the query anayzer
Select * From CMRC_Products Where mfg = 'DeltaFaucet'  And (Family Like '%Showerhead%' Or Family Like '%Handshower%')

it returned 12 rows as it should
0
dplsrAuthor Commented:


this should return 12 rows too


Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''%' + @StrMfg + '%'' And (Family '
0
dplsrAuthor Commented:


Got to take a break. Be back later. Thanks for all the help! I am wondering if the addition parameter should be somewhere else on the page?
instead of

Set @SQL = 'Select * From CMRC_Products Where Mfg Like ''%' + @StrMfg + '%'' And (Family '

0
SjoerdVerweijCommented:
Are you SURE the Execute (@SQL) line is still in the stored procedure? It looks like it's simply not running.
0
dplsrAuthor Commented:


That was it!  some where along the line in cutting and pasting it got left out!   Thanks again!
0
SjoerdVerweijCommented:
No sweat!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.