SP Dynamic SQL with LIKE Operator

175 pts.
Tags:
Dynamic SQL
SQL Server 2005
SQL stored procedures
Stored Procedure variables
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    Are you getting an error? What is the error? OR does it just seem to run forever?
    5,220 pointsBadges:
    report
  • Eabouzeid
    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'
    175 pointsBadges:
    report
  • Eabouzeid
    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
    175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following