Start Free Trial

asked on

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

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

I don't think you can use % with in
you have to use it with like

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 .......

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

ASKER

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%

acampoma's should work.

Wait, no it will not. You'll have to transform to

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

ASKER

That's beyond my expertise, for sure! can you give an example?

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)

ASKER

Thanks SjoerdVerweij !

I am getting an error:

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

Ok... could you run it in Query Analyzer and post what @SQL contains?

ASKER

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

ASKER

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

ASKER

i change on like ti in sorry i will resend

ASKER

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

ASKER

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

ASKER

I appalogize again! I was trying to fix it myself

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

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!

ASKER

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

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

ASKER

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?

ASKER

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

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

ASKER

sorry! now it returns nothing

ASKER

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

ASKER

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

Are you sure some records for this exist? Did you try cutting, pasting and running it in Query Analyzer?

ASKER

yes, if i take the mfg parts out it returns the strreqoptions for all mfg including deltafaucet. there are several

ASKER

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

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...

ASKER

yes

ASKER

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

Really, the only way they wouldn't show up is if deltafaucet is spelled differently, e.g. "delta faucet".

ASKER

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

ASKER

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

ASKER

i took of the %% just to try

You still have to keep the ' ' when you remove the %s.

ASKER

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

Weird, weird, weird.

What does this return?

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

ASKER

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

ASKER

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.

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 '

ASKER

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

ASKER

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

Does

Select * From CMRC_Products Where Mfg Like 'D%'

return anything?

ASKER

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

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?

ASKER

no
returned nothing

Than that's your problem -- if that doesn't return anything, neither will the dynamic SQL.

ASKER

i used it in dynamic sql

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

ASKER

i will just do a regular query

ASKER

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

ASKER

this should return 12 rows too

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

ASKER

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 '

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

ASKER

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

No sweat!