October 28, 2009 at 12:21 pm
It appears that using the LIKE in a condition with wildcards and a variable inside of dynamic sql doesn't work, although it doesn't give an error. Here's an example.
The column called code has values like A0B01C02,A0B02C2D05,A0B02C2D05, etc and I am trying to match on rows containing a subset like 'B1'. When I do this it works and returns results as expected.
set @sql='select * from table where code like ''%B01%'''
exec sp_executesql @sql
If I hardcode the value of the variable
set @code='B01'
and modify the sql statement to concatenate the quotes and wildcards:
set @sql='select * from table where code like ' +''''+ '%'+@code + '%' + ''''
exec sp_executesql @sql
This returns the results as expected, but I had to hard code the variable. However, when I need to make the match using a variable for B01 and that the variable is set with a select statement, I don't get any results returned. I define an nvarchar like this:
set @code=(select top 1 code from anotherTable where USERID=@PersonId)
I confirmed that the select statement above returns the expected code, however. There is no error, but the query is "executed successfully". Am I missing something in the syntax for the where clause?
October 28, 2009 at 12:46 pm
You may want to spend a little time reading about sp_executesql in BOL (Books Online). Based on the example there this is the code I came up with, but with nothing to test against, I have no way of knowing if it works.
October 28, 2009 at 12:55 pm
Hi takutoyamada
If the search criterion does not contain any further wild cards (like "%foo%bar%") CHARINDEX function usually works faster than LIKE.
Greets
Flo
October 28, 2009 at 12:59 pm
Make the following changes to the code I provided:
set @incode = '%' + 'B01' + '%'
--set @incode = '%' + (select top 1 code from anotherTable where USERID=@PersonId) + '%'
October 28, 2009 at 2:59 pm
Thanks Lynn - I'm trying to implement this with the use of multiple parameters by expanding upon your example. How would you define more than one? BOL also only has examples with one parameter.
October 28, 2009 at 3:23 pm
Nevermind, Lynn- I figured it out that multiple parameters can work!
Thanks for all your great help.
October 28, 2009 at 3:27 pm
Actually, having just looked at BOL, there are several examples using multiple variables. Looks like they aren't necessarily using variables like I had setup, but they are there. Also, take a look at the syntax of the sp_executesql procedure. It shows how you would do it with multiple variables.
October 28, 2009 at 3:32 pm
takutoyamada (10/28/2009)
Nevermind, Lynn- I figured it out that multiple parameters can work!Thanks for all your great help.
Glad I could help. Looks like you found another reference that assisted as well.
October 28, 2009 at 4:15 pm
Flo- that made a huge difference in performance!
Tak
October 29, 2009 at 2:46 am
Hi Tak
Glad we could help. Thanks for the feedback!
Flo
October 29, 2009 at 7:32 am
Hello all,
I found this post searching for a solution to correctly escape quotes surrounding wildcards on either side of a parameter. Here is my code:
IF @companyName IS NOT NULL
SELECT @sql=@sql+' AND c.CompanyName LIKE "%" + @xcompanyName + "%"'
IF @requestorName IS NOT NULL
SELECT @sql=@sql+' AND o.LastName LIKE "%" + @xrequestorName + "%"
OR o.FirstName LIKE "%" + @xrequestorName + "%"
OR o.FirstName + " " + o.LastName "%" + @xrequestorName + "%"'
The procedure compiles fine but when I execute it I get error near "%", so that tells me I haven't escaped my quotes correctly. I'm new at this so that is why I am posting this.
Thanks.
October 29, 2009 at 9:20 am
Hi
As Lynn already suggested. You should use sp_executesql instead of concatenating values into dynamic SQL strings. Concatenating SQL with values is always a solution for SQL injection and can cause dramatic problems.
For further information follow this link: sp_executesql
Greets
Flo
October 29, 2009 at 9:39 am
Hi
Give this a try:
-- some vars
DECLARE
@companyName NVARCHAR(100)
,@requestorName NVARCHAR(100)
,@sql NVARCHAR(1000);
-- some parameter
SELECT
@companyName = NULL
,@requestorName = 'AnyRequestor';
-- any base sql statement
SELECT
@sql = 'Build Your Base Statement WHERE AnyThing = SomeThing';
-- add the parameters
SELECT
-- companyName
+ CASE WHEN @companyName IS NOT NULL
THEN ' AND c.CompanyName LIKE @companyName'
ELSE '' END
-- requestorName
+ CASE WHEN @requestorName IS NOT NULL
THEN ' AND (
o.LastName LIKE @requestorName
OR o.FirstName LIKE @requestorName
OR o.FirstName + '' '' + o.LastName = @requestorName
)'
ELSE '' END;
--PRINT @sql
EXECUTE sp_executesql
,N'@companyName NVARCHAR(100), @requestorName NVARCHAR(100)'
,@companyName = @companyName
,@requestorName = @requestorName;
Greets
Flo
October 29, 2009 at 9:53 am
Thanks for all the input. I'm having partial success with my procedure now, but I think I should post the entire code since there were questions about string concatenation and the last example posted only included two of the parameters I am dealing with. Here is the entire procedure:
ALTER PROCEDURE [dbo].[artworksearch]
-- Add the parameters for the stored procedure here
@beginDate datetime = NULL,
@endDate datetime = NULL,
@companyName nvarchar(100) = NULL,
@requestorName nvarchar(50) = NULL,
@debug bit = 0
AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql='SELECT r.RequestID, r.RequestorID, o.FirstName, o.LastName, o.Telephone, o.Email, c.CompanyName, r.RequestDate, r.FilesRequested, r.FranklinSite, r.CustomerType, r.Approved, r.FilesSent, r.SentDate, r.Comments
FROM dbo.tblRequest AS r
JOIN dbo.tblRequestor AS o ON r.RequestorID = o.RequestorID
JOIN dbo.tblCompany AS c ON o.CompanyID = c.CompanyID
WHERE 1 = 1'
IF @beginDate IS NOT NULL AND @endDate IS NOT NULL
SELECT @sql=@sql+' AND r.RequestDate BETWEEN @xbeginDate AND @xendDate'
IF @beginDate IS NOT NULL AND @endDate IS NULL
SELECT @sql=@sql+' AND r.RequestDate BETWEEN @xbeginDate AND GETDATE()'
IF @companyName IS NOT NULL
SELECT @sql=@sql+' AND c.CompanyName LIKE ''%'' + @xcompanyName + ''%'''
IF @requestorName IS NOT NULL
SELECT @sql=@sql+' AND o.LastName LIKE ''%'' + @xrequestorName + ''%''
OR o.FirstName LIKE ''%'' + @xrequestorName + ''%''
OR o.FirstName + '' '' + o.LastName LIKE ''%'' + @xrequestorName + ''%'''
IF @beginDate IS NOT NULL AND @endDate IS NOT NULL AND @companyName IS NOT NULL
SELECT @sql=@sql+' AND r.RequestDate BETWEEN @xbeginDate AND @xendDate AND c.CompanyName LIKE ''%'' + @xcompanyName + ''%'''
IF @beginDate IS NOT NULL AND @endDate IS NULL AND @companyName IS NOT NULL
SELECT @sql=@sql+' AND r.RequestDate BETWEEN @xbeginDate AND GETDATE() AND c.CompanyName LIKE ''%'' + @xcompanyName + ''%'''
IF @beginDate IS NOT NULL AND @endDate IS NOT NULL AND @requestorName IS NOT NULL
SELECT @sql=@sql+' AND o.LastName LIKE ''%'' + @xrequestorName + ''%''
OR o.FirstName LIKE ''%'' + @xrequestorName + ''%''
OR o.FirstName + '' '' + o.LastName LIKE ''%'' + @xrequestorName + ''%'' AND r.RequestDate BETWEEN @xbeginDate AND @xendDate '
IF @debug = 1
PRINT @sql
SELECT @paramlist='@xbeginDate datetime,
@xendDate datetime,
@xcompanyName nvarchar(100),
@xrequestorName nvarchar(50)'
EXEC sp_executesql @sql,@paramlist,
@beginDate,@endDate,@companyName,@requestorName
Thanks for the feedback.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply