October 4, 2010 at 8:37 am
I’ve got a large parameterized dynamic SQL query that’s having trouble using the LIKE operator.
For some odd reason when I pass in a phone number, the LIKE operator doesn’t work.
i.e. the phone number '724-342-6833'
will get trimmed down to 7243426833 and passed into the dynamic query
The dynamic query’s LIKE operator
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE @Phone + ''%''or REPLACE(SecondPhone,''-'','''') LIKE @Phone + ''%'' ) '
Should work, and in fact if I take the output query and setup a variable and pass in the phone number with % on the end it does,
Just not in the dynamic query. I've also tried concatenating a % onto the parameter variable and just using straight LIKE operators against the parameters with the same lack of success.
If you really need it, I can come up with the DDL to make the tables… all the inserts to fill them will take quite a while longer though.
I was hoping somebody was aware of a behavior with dynamic SQL and the LIKE operator that I wasn’t.
Thanks!
ALTER PROCEDURE [usp_Client]
@ClientSeq int=NULL,
@ClientName varchar(37)=NULL,
@Phone varchar(14)=NULL,
@GreatPlainsID varchar(10)=NULL,
@ClientID varchar(9)=NULL,
@ConglomerateID varchar(9)=NULL,
@ConglomerateFlag tinyint=NULL,
@License varchar(14)=NULL,
@State varchar(4)=NULL,
@Attrition varchar(1)=NULL,
@ClientType varchar(1)=NULL,
@Active tinyint=NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @CMD nvarchar(max)
declare @SELECT nvarchar(max)
declare @WHERE nvarchar(max)
SELECT @SELECT = N'
SELECT SeqNumber,
ClientType,
ClientID,
ClientName,
GreatPlainsID,
ProspectSeq,
ProspectID,
AccountTypeNO,
AccountType,
StatusNO,
Status,
AttentionName,
Heading,
ConglomerateID,
LocalPhone,
SecondPhone,
Fax,
URLAddress,
PhysicalStreet,
PhysicalCity,
PhysicalState,
PhysicalZip,
MailingStreet,
MailingCity,
MailingState,
MailingZip,
LanguageNO,
Language,
PayTerm,
PayTermName,
Alert,
Referral,
ASCNet,
BadDebt,
Legal,
Active,
LastUpdated,
Watch,
CompanyCode,
Territory,
Region,
ManagerName,
ConglomerateFlag,
UserQuantity,
ProductType,
AgencyPassCode,
StartTime,
EndTime,
GMTOffset
FROM (SELECT SeqNumber = ai_agn_seq,
ClientType = Ai_ClientType,
ClientID = Ai_cust_id,
ClientName = Ai_Name,
GreatPlainsID = Ai_mac_id,
ProspectSeq = ai_prospect_seq,
ProspectID = ai_prospect_id,
AccountTypeNO = ai_type,
AccountType = at_agn_type,
StatusNO = ai_status,
Status = st_name,
AttentionName = Ai_attn,
Heading = ai_heading,
ConglomerateID = AI.ai_conglom,
LocalPhone = Ai_loc_phn,
SecondPhone = Ai_800_phn,
Fax = Ai_fax_phn,
URLAddress = ai_url,
PhysicalStreet = Ai_Street1,
PhysicalCity = Ai_City,
PhysicalState = Ai_State,
PhysicalZip = Ai_Zipcode,
MailingStreet = Ai_mailstreet1,
MailingCity = Ai_mailcity,
MailingState = Ai_mailstate,
MailingZip = Ai_mailzipcode,
LanguageNO = Ai_language,
Language = alg_language,
PayTerm = ai_payterms,
Alert = ai_alert,
Referral = ai_referral,
ASCNet = ai_ascnet,
BadDebt = ai_baddebt,
Legal = ai_pendlegal,
Active = Ai_Active,
LastUpdated = ai_ts_update,
Watch = ai_watch,
CompanyCode = ai_companycode,
ConglomerateFlag = isnull(Cong.CongFlag, 0),
UserQuantity = ap_users,
ProductType = ap_type,
ai_territory_seq,
PayTermName=PT.pm_term_name,
AgencyPassCode = ai_passcode,
StartTime = ai_str_tim,
EndTime = ai_end_tim,
GMTOffset = ai_gmt_dif
FROM PubData..Agn_Info AS AI
LEFT JOIN pubdata..agn_payterms PT ON AI.ai_payterms= PT.pm_term_seq
LEFT JOIN Pubdata..agn_status AS ST ON AI.ai_status = ST.st_seq_num
LEFT JOIN Pubdata..agn_agntype AS AT ON AI.ai_type = AT.at_type_seq
LEFT JOIN Pubdata..Agn_Language AS AL ON AI.ai_language = AL.alg_seq_num
LEFT JOIN (SELECT ap_type, ap_users, ap_agn_seq FROM Pubdata..agn_prod WHERE ap_main_type = 1) PROD ON PROD.ap_agn_seq = AI.ai_agn_seq
LEFT JOIN (SELECT CongFlag = CASE WHEN count(cong.ai_conglom) > 1 THEN cast(1 AS BIT) WHEN count(cong.ai_conglom) <= 1 THEN cast(0 AS BIT) END, ai_conglom FROM PubData..agn_info cong GROUP BY ai_conglom) Cong ON AI.ai_conglom = Cong.ai_conglom and Ai.ai_cust_id = Cong.ai_conglom
UNION ALL
SELECT SeqNumber = '''',
ClientType = ''P'',
ClientID = '''',
ClientName = Ai_Name,
GreatPlainsID = '''',
ProspectSeq = ai_agn_seq,
ProspectID = Ai_cust_id,
AccountTypeNO = 0,
AccountType = '''',
StatusNO = 0,
Status = '''',
AttentionName = Ai_attn,
Heading = ai_heading,
ConglomerateID = ai_conglomerate,
LocalPhone = Ai_loc_phn,
SecondPhone = '''',
Fax = Ai_fax_phn,
URLAddress = ai_url,
PhysicalStreet = ai_altstreet1,
PhysicalCity = ai_altcity,
PhysicalState = ai_altstate,
PhysicalZip = ai_altzipcode,
MailingState = Ai_Street1,
MailingCity = Ai_City,
MailingStreet = Ai_State,
MailingZip = Ai_Zipcode,
LanguageNO = ai_language,
Language = alg_language,
PayTerm = '''',
Alert = 0,
Referral = 0,
ASCNet = 0,
BadDebt = 0,
Legal = 0,
Active = ''1'',
LastUpdated = ''1/1/1900'',
Watch = 0,
CompanyCode = ai_companycode,
ConglomerateFlag = 0,
UserQuantity = 0,
ProductType = '''',
ai_territory_seq,
PayTermName='''',
AgencyPassCode='''',
StartTime = '''',
EndTime = '''',
GMTOffset = 0
FROM Prospects..Agn_Info AS AI
LEFT JOIN Pubdata..Agn_Language AS AL ON AI.ai_language = AL.alg_seq_num
WHERE NOT EXISTS (SELECT k.ai_agn_seq FROM pubdata..agn_info J
INNER JOIN prospects..agn_info K ON j.ai_prospect_seq = k.ai_agn_seq
WHERE ai.ai_agn_seq = k.ai_agn_seq ))
AS EVERY LEFT JOIN
(SELECT atr_seq_num,
Territory = atr_territory_name,
Region = arg_region,
ManagerName = el_fullname
FROM pubdata..Agn_Territory TER
inner join pubdata..agn_regionTerritory REGT ON TER.atr_seq_num = REGT.art_territory
inner join pubdata..agn_regions AS REG ON REGT.art_region = REG.arg_id
inner join pubdata..emplist AS EMP ON REG.arg_manager = EMP.el_emp_seq) AREA
ON EVERY.ai_territory_seq = AREA.atr_seq_num '
SET @WHERE = N' WHERE 1=1 '
IF @ClientSeq IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND (SeqNumber = @ClientSeq or ProspectSeq = @ClientSeq) ORDER BY ClientType ASC '
END
IF @ClientName IS NOT NULL
BEGIN
SET @clientName = @ClientName + '%'
SET @WHERE = @WHERE + N' AND ClientName like @ClientName'
END
IF @Phone IS NOT NULL
BEGIN
SET @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'-',''),')',''),'(',''),' ','')
SET @Phone = @Phone + '%'
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE @Phone or REPLACE(SecondPhone,''-'','''') LIKE @Phone ) '
END
IF @GreatPlainsID IS NOT NULL
BEGIN
SET @GreatPlainsID = @GreatPlainsID + '%'
SET @WHERE = @WHERE + N' AND GreatPlainsID like @GreatPlainsID '
END
IF @ClientID IS NOT NULL
BEGIN
SET @ClientID = @ClientID + '%'
SET @WHERE = @WHERE + N' AND (ClientID like @ClientID or ProspectID like @ClientID) '
END
IF @ConglomerateID IS NOT NULL
BEGIN
SET @ConglomerateID = @ConglomerateID + '%'
SET @WHERE = @WHERE + N' AND ConglomerateID like @ConglomerateID and ConglomerateFlag = @ConglomerateFlag '
END
IF @License IS NOT NULL
BEGIN
SET @License = @License + '%'
SET @WHERE = @WHERE +
N' AND SeqNumber in (SELECT DISTINCT ap_agn_seq FROM pubdata..agn_prod WHERE Ap_Tam_Lic like @License ) '
END
IF @State IS NOT NULL
BEGIN
SET @State = @State + '%'
SET @WHERE = @WHERE + N' AND (PhysicalState like @State or MailingState like @State) '
END
IF @Attrition IS NOT NULL
BEGIN
if @Attrition = 'A'
BEGIN
SET @WHERE = @WHERE + N' AND Alert = 1 '
END
if @Attrition = 'W'
BEGIN
SET @WHERE = @WHERE + N' AND Watch = 1 '
END
if @Attrition = 'B'
BEGIN
SET @WHERE = @WHERE + N' AND Alert = 1 OR Watch = 1 '
END
END
IF @ClientType IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND ClientType = @ClientType '
END
IF @Active IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND Active = @Active '
END
-- Don't return all clients if no search fields entered
IF @WHERE = N' AND 1=1 '
BEGIN
SET @WHERE = N' AND 1=0 '
END
SET @CMD = @SELECT + @WHERE
PRINT @CMD
EXEC sp_executesql @cmd,N'@ClientName varchar(37),
@ClientSeq int,
@Phone char(14),
@GreatPlainsID char(10),
@ClientID char(9),
@ConglomerateID varchar(9),
@ConglomerateFlag tinyint,
@License char(14),
@State char(4),
@Attrition char(1),
@ClientType char(1),
@Active tinyint',
@ClientName=@ClientName,
@ClientSeq=@ClientSeq,
@Phone=@Phone,
@GreatPlainsID=@GreatPlainsID,
@ClientID=@ClientID,
@ConglomerateID=@ConglomerateID,
@ConglomerateFlag=@ConglomerateFlag,
@License=@License,
@State=@State,
@Attrition=@Attrition,
@ClientType=@ClientType,
@Active=@Active
October 4, 2010 at 9:25 am
For some reason I'm unable to view the code you pasted...however the sample WHERE clause you posted seems to have some issues...I've put in some sample code below modifying the sample you provided and the LIKE comparison works fine with dynamic SQL...
CREATE TABLE #Phone(LocalPhone nvarchar(50),SecondPhone nvarchar(50));
DECLARE @Phone nvarchar(50), @sql nvarchar(max) = '', @WHERE nvarchar(max)='';
SET @Phone = N'7243426833';
INSERT #Phone(LocalPhone,SecondPhone)
SELECT '724-342-6833','724-342-6834'
UNION ALL
SELECT '723-342-6833','724-342-6833'
UNION ALL
SELECT '724-342-6834','725-342-6833'
SELECT @sql = @sql + ' SELECT * FROM #Phone WHERE 1=1 ';
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE '''+@Phone + '%'' OR REPLACE(SecondPhone,''-'','''') LIKE '''+@Phone + '%'' ) '
PRINT @sql;
EXEC(@Sql);
-- DROP TABLE #Phone;
October 4, 2010 at 9:25 am
If you're setting up a dynamic query, you might as well just replace the variables with the static values anyway. I also thought sp_exec or executesql ran in its own context, and could not call variables from the parent query.
i.e. isntead of:
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE @Phone or REPLACE(SecondPhone,''-'','''') LIKE @Phone ) '
use:
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE '''+@Phone+''' or REPLACE(SecondPhone,''-'','''') LIKE '''+@Phone+''') '
Without having your DDL I couldn't fully test it, but this worked as a quick check on my end..although so did your example that you gave, so that doesn't mean much.
edit: winash beat me to it with the same idea 🙂
October 4, 2010 at 9:34 am
I'm trying to use parameterized dynamic SQL.
i.e.
http://www.sommarskog.se/dyn-search-2005.html
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Not using Parameterized Dynamic SQL is just plain bad.
using
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE '''+@Phone + '%'' OR REPLACE(SecondPhone,''-'','''') LIKE '''+@Phone + '%'' ) '
Generates stale execution plans because the next phone number is a different statistical grouping and I don't get a stored execution plan for the dynamic query.
using
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE @Phone + ''%'' OR REPLACE(SecondPhone,''-'','''') LIKE @Phone + ''%'' ) '
Will work better because I get an execution plan for a query where there's a parameter for the LocalPhone and Secondphone fields.
October 4, 2010 at 9:50 am
Same code snippet as above - using sp_executesql and parameters...this uses the same type of comparison used in your SP code (appending a % for the like comparison) and it works...
In your SP code (which I can now see) you're appending the % to the variable and using it for a comparison - in the WHERE clause snippet you provide you're trying to append the % within the dynamic SQL...any specific reason for this?
Also - I'm not sure I understood the rationale behind wanting to persist the execution plan in cache if the statistical grouping of the results would vary widely based on the parameters passed in...it might be better to let SQL Server decide on a fresh plan each time based on the parameters passed in? Or have I totally misunderstood what you were saying?
CREATE TABLE #Phone(LocalPhone nvarchar(50),SecondPhone nvarchar(50));
DECLARE @Phone nvarchar(50), @sql nvarchar(max) = '', @WHERE nvarchar(max)='',@ParamDef nvarchar(500);
SET @Phone = N'7243426833';
SET @Phone = @Phone+'%';
INSERT #Phone(LocalPhone,SecondPhone)
SELECT '724-342-6833','724-342-6834'
UNION ALL
SELECT '723-342-6833','724-342-6833'
UNION ALL
SELECT '724-342-6834','725-342-6833'
SELECT @sql = @sql + ' SELECT * FROM #Phone WHERE 1=1 ';
SET @WHERE = @WHERE + N' AND (REPLACE(LocalPhone,''-'','''') LIKE @Phone OR REPLACE(SecondPhone,''-'','''') LIKE @Phone ) '
PRINT @sql;
SET @ParamDef = '@Phone nvarchar(50)';
EXEC sp_executesql @sql,@ParamDef,@Phone=@Phone;
--DROP TABLE #Phone;
October 4, 2010 at 10:14 am
In this case we're talking about what is a massive catch-all query.
The trick here is to build different execution plans for each pattern of query generated, not a specific plan for each phone number. That's besides the point that injection is potentially introduced if we append the parameter value to the dynamic query vs parameterizing it and passing it in.
I do not want to concatenate the values of the parameters onto the dynamic query, I want to build a dynamic query for the various combinations of nullable parameters specified into the stored proc.
My question is about if using LIKE in a parameterized dynamic query (as my dynamic code is generating) does it just not work?
What I'm trying to do is well documented at this website.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
And this website
http://www.sommarskog.se/dyn-search-2005.html
What I don't understand is why when I use LIKE with parameters it doesn't seem to work.
I can't tell if I have a hidden syntax error that causes a test case for phone number that just doesn't work, or something else.
I can write very simple test cases that do seem to work, which tends me to think it is a syntax error, but I could be wrong.
October 4, 2010 at 10:18 am
Out of curiosity (unless I missed it)..what error are you getting?
October 4, 2010 at 10:20 am
Not getting an error...
If I was... I could track it down.
Not getting any records back when I pass in a phone number I know should return them.
October 4, 2010 at 10:28 am
You are using the same exact variable names at the very end of the sp_executesql..Gail's article used a different set (prefixed with _)
Try replacing your executesql statement with this:
EXEC Sp_executesql
@cmd,
N'@_ClientName varchar(37),
@_ClientSeq int,
@_Phone char(14),
@_GreatPlainsID char(10),
@_ClientID char(9),
@_ConglomerateID varchar(9),
@_ConglomerateFlag tinyint,
@_License char(14),
@-State char(4),
@_Attrition char(1),
@_ClientType char(1),
@_Active tinyint',
@_ClientName=@ClientName,
@_ClientSeq=@ClientSeq,
@_Phone=@Phone,
@_GreatPlainsID=@GreatPlainsID,
@_ClientID=@ClientID,
@_ConglomerateID=@ConglomerateID,
@_ConglomerateFlag=@ConglomerateFlag,
@_License=@License,
@_State=@State,
@_Attrition=@Attrition,
@_ClientType=@ClientType,
@_Active=@Active
October 4, 2010 at 10:34 am
It shouldn't matter... I've got at least 2 dozen other search queries that do that exact thing.
However, I swapped them out for @xVARIABLE
same issue...
I've decided I hate the actual dynamic query (it's too hard to troubleshoot with the UNION ALL in the middle and all the subqueries)... so I'm tearing it back down into a multi-stage filing of a temp table and seeing if I can get better results from that.
October 4, 2010 at 10:39 am
If the phone number is always stored in format 999-999-9999, then the code below should work without dynamic SQL.
declare @phone nvarchar(50)
set @phone = N'7243426833';
select @phone = stuff(stuff(@phone,7,0,'-'),4,0,'-')
declare @pt table (localphone nvarchar(50),secondphone nvarchar(50));
insert @pt(localphone,secondphone)
select '724-342-6833','724-342-6834'union all
select '723-342-6833','724-342-6833'union all
select '724-342-6834','725-342-6833'
select
*
from
@pt
where
@phone in (localphone,secondphone)
October 4, 2010 at 10:42 am
If you run the query statically does it work? In other words, if you assume knowledge of the parameters, type out the query yourself with the like statement, and run it, does it find the rows you are expecting?
If yes, then the problem is with how the dynamic sql is build, and if you test outputting the finished sql statement you might find the error.
If no, then the data isnt matching the way you are expecting it to. Note that you are only stripping off dashes. Any spaces or brackets in phone number data could still be throwing you off.
October 4, 2010 at 10:49 am
Actually, THIS might be the problem
EXEC sp_executesql @cmd,N'@ClientName varchar(37),
@ClientSeq int,
@Phone char(14),
@GreatPlainsID char(10),
@ClientID char(9),
@ConglomerateID varchar(9),
@ConglomerateFlag tinyint,
@License char(14),
@State char(4),
@Attrition char(1),
@ClientType char(1),
@Active tinyint',
@ClientName=@ClientName,
@ClientSeq=@ClientSeq,
@Phone=@Phone,
@GreatPlainsID=@GreatPlainsID,
@ClientID=@ClientID,
@ConglomerateID=@ConglomerateID,
@ConglomerateFlag=@ConglomerateFlag,
@License=@License,
@State=@State,
@Attrition=@Attrition,
@ClientType=@ClientType,
@Active=@Active
You convert your phone number like to a char(14), which would output LIKE '1234567899% ' as its comparison string I think (3 blank spaces at the end). Could have the same issue with GreatPlainsID
October 4, 2010 at 10:49 am
Ok, did some more research/testing and I see an issue now.
Out of curiosity..you set the phone number as:
SET @Phone = @Phone + '%'
Shouldn't this be
SET @Phone = '''%' + @Phone + '%'''
edit: To answer one of your original questions, you need to add the %'s inside the search string itself, and then pass that to the SP. I can get this working in asp.net but don't have the table structures/etc to test in sql.
re-edit: From what I can tell, explicit quotes are needed when you're not doing an = comparison.
October 4, 2010 at 11:13 am
Derrick Smith (10/4/2010)
Ok, did some more research/testing and I see an issue now.Out of curiosity..you set the phone number as:
SET @Phone = @Phone + '%'Shouldn't this be
SET @Phone = '''%' + @Phone + '%'''re-edit: From what I can tell, explicit quotes are needed when you're not doing an = comparison.
I don't think so
I'm changing the value of the parameter to create a string that's
'#######%'
then I'm using LIKE on it as a parameter.
It's logically equivalent of something like this.
CREATE PROCEDURE Test
@Phone varchar(20)
AS
SELECT
*
FROM
Clients
WHERE
Phone LIKE @Phone
GO
DECLARE @Phone varchar(20)
SET @Phone = '123-456-7890'
SET @Phone = REPLACE(@Phone,'-','')
SET @Phone = @Phone + '%'
EXEC Test @Phone = @Phone
the string value of @Phone which is now 1234567890%
should be in the stored procedure as that and used in the LIKE comparison.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply