Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I use Wildcards %% with dynamic SQL?

Posted on 2004-12-01
61
Medium Priority
?
485 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:dplsr
  • 37
  • 19
  • 2
  • +3
61 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12719726
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
 
LVL 6

Expert Comment

by:acampoma
ID: 12719740
I don't think you can use % with in
you have to use it with like
0
 
LVL 4

Expert Comment

by:cjm30305
ID: 12719754
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 6

Expert Comment

by:acampoma
ID: 12719763
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
 

Author Comment

by:dplsr
ID: 12719842


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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12719865
acampoma's should work.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12719876
Wait, no it will not. You'll have to transform to

... Like '%ShowerHead%' And ... Like '%BlockHead%' And ...
0
 

Author Comment

by:dplsr
ID: 12719952
That's beyond my expertise, for sure! can you give an example?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12720037
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
 

Author Comment

by:dplsr
ID: 12720113
Thanks SjoerdVerweij !

I am getting an error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Like'.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12720799
Ok... could you run it in Query Analyzer and post what @SQL contains?
0
 

Author Comment

by:dplsr
ID: 12720894
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
 

Author Comment

by:dplsr
ID: 12720911
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
 

Author Comment

by:dplsr
ID: 12721051
i change on like ti in sorry i will resend
0
 

Author Comment

by:dplsr
ID: 12721055
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
 

Author Comment

by:dplsr
ID: 12721058
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
 

Author Comment

by:dplsr
ID: 12721071
I appalogize again! I was trying to fix it myself
0
 
LVL 18

Assisted Solution

by:SjoerdVerweij
SjoerdVerweij earned 1400 total points
ID: 12721873
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
 
LVL 3

Expert Comment

by:cheeky-monkey
ID: 12721958
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
 
LVL 6

Accepted Solution

by:
PePi earned 600 total points
ID: 12722060
You may also want to check your sql statement. I think it should be OR and not AND.

HTH!
0
 

Author Comment

by:dplsr
ID: 12722344

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
 

Author Comment

by:dplsr
ID: 12722374
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
 

Author Comment

by:dplsr
ID: 12726264
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12727491
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
 

Author Comment

by:dplsr
ID: 12727811
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
 

Author Comment

by:dplsr
ID: 12727847
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12727860
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
 

Author Comment

by:dplsr
ID: 12728084

sorry! now it returns nothing
0
 

Author Comment

by:dplsr
ID: 12728122
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
 

Author Comment

by:dplsr
ID: 12728711
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12728721
Are you sure some records for this exist? Did you try cutting, pasting and running it in Query Analyzer?
0
 

Author Comment

by:dplsr
ID: 12728752
yes, if i take the mfg parts out it returns the strreqoptions for all mfg including deltafaucet. there are several
0
 

Author Comment

by:dplsr
ID: 12728844


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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12729096
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
 

Author Comment

by:dplsr
ID: 12729178
yes
0
 

Author Comment

by:dplsr
ID: 12729215


You want me to dump mfg fields? I don't understand
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12729229
Really, the only way they wouldn't show up is if deltafaucet is spelled differently, e.g. "delta faucet".
0
 

Author Comment

by:dplsr
ID: 12729307
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
 

Author Comment

by:dplsr
ID: 12729332
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
 

Author Comment

by:dplsr
ID: 12729335
i took of the %% just to try
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12729382
You still have to keep the ' ' when you remove the %s.
0
 

Author Comment

by:dplsr
ID: 12729518
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12729606
Weird, weird, weird.

What does this return?

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

Author Comment

by:dplsr
ID: 12729749
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
 

Author Comment

by:dplsr
ID: 12729823
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12730074
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
 

Author Comment

by:dplsr
ID: 12730725
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
 

Author Comment

by:dplsr
ID: 12730828


I am going to test this with some other parameter like in or out of stock.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12730999
Does

Select * From CMRC_Products Where Mfg Like 'D%'

return anything?
0
 

Author Comment

by:dplsr
ID: 12731002


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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12731116
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
 

Author Comment

by:dplsr
ID: 12731149
no
 returned nothing
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12731163
Than that's your problem -- if that doesn't return anything, neither will the dynamic SQL.
0
 

Author Comment

by:dplsr
ID: 12731275

i used it in dynamic sql

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


0
 

Author Comment

by:dplsr
ID: 12731282
i will just do a regular query
0
 

Author Comment

by:dplsr
ID: 12731321


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
 

Author Comment

by:dplsr
ID: 12731367


this should return 12 rows too


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

Author Comment

by:dplsr
ID: 12731392


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
 
LVL 18

Expert Comment

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

Author Comment

by:dplsr
ID: 12731803


That was it!  some where along the line in cutting and pasting it got left out!   Thanks again!
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12731882
No sweat!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question