SP Dynamic SQL with LIKE Operator
Hi. I want to create a stored procedure whereby the variable comes after the LIKE operator. For example, the code would look something like this:
CREATE PROCEDURE SPFIND
@VARIABLE1 VARCHAR
SELECT *
FROM TABLE1
WHERE FIELD1 LIKE '%@VARIABLE1%'
Then to execute the procedure,
EXEC SPFIND 'VARIABLENAME'
I can't get this to work using the LIKE operator. I would appreciate any help.
Software/Hardware used:
SQL Server 2005
Software/Hardware used:
SQL Server 2005
ASKED:
June 7, 2010 12:38 PM
UPDATED:
June 8, 2010 7:26 PM
Answer Wiki:
One problem is the parameter definition - you need to specify the maximum dimension of the VARCHAR parameter in, otherwise it will only use the first character.
I would suggest you dynamically create the comparison string, as follows:
<pre>CREATE PROCEDURE SPFIND
@VARIABLE1 VARCHAR(255)
as
declare @Variable2 varchar(255);
set @Variable2 = '%' + coalesce(@Variable1,'') + '%';
SELECT *
FROM TABLE1
WHERE FIELD1 LIKE @Variable2;</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Are you getting an error? What is the error?
OR
does it just seem to run forever?
No. I just don’t get any results. The only way I’m able to get results is if I execute the stored procedure as shown below:
EXEC spFIND @variable1=’%variablename’
I think it should work by simply saying EXEC spFIND @variable1=’variablename’
Thank you for your help. I tried what you wrote and it worked just fine. Another solution I found that worked was this modification:
CREATE PROCEDURE SPFIND
@VARIABLE1 VARCHAR(20)
SELECT *
FROM TABLE1
WHERE FIELD1 LIKE LIKE ‘%’ + @VARIABLE1+ ‘%’
Then to execute I would just pass the variable like this:
EXEC VARIABLENAME