getting error when executing dynamic query in sql procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All,

I'm using the below code to run the sql procedure.

ALTER PROCEDURE [dbo].[USP_Get_xxx] @qryType varchar(50), @searchType varchar(50), @searchText varchar(50), @searchByCity varchar(50),  @advertiserId int
AS
 Declare @SQLQuery AS NVarchar(4000)

BEGIN	
 Set @SQLQuery =  ' SELECT AR.* FROM Tbl_RRRR AR  LEFT JOIN Tbl_PPP APS ON  AR.Id = APS. Id  WHERE ( AR.Company_Name LIKE @searchText  OR APS.Product_or_Service_Name   LIKE  @searchText   ) '

EXEC  sp_executesql @SQLQuery

I'm getting the below error: Must declare the scalar variable "@searchText ".

If any one please suggest.

Thank You in advance,
Varshini M.

Posted 15-Mar-11 4:20am

Varshini2009

Updated 15-Mar-11 4:22am

m@dhu

v2

Add a Solution

3 solutions

Solution 1

Try out below.

Set @SQLQuery =  ' SELECT AR.* FROM Tbl_RRRR AR  LEFT JOIN Tbl_PPP APS ON  AR.Id = APS. Id  WHERE ( AR.Company_Name LIKE ' + '''' +  @searchText + '''' + ' OR APS.Product_or_Service_Name   LIKE ' + '''' +  @searchText + '''' +  ) '

Search text should be clearly define in the SQL statement. It is passed as parameter so you need to make query such that the value of it placed in your @SQLQuery variable.

Hope it helps.

Posted 15-Mar-11 4:47am

^That's Aragon

Solution 3

Why do you need dynamic SQL at all?

SQL

ALTER PROCEDURE [dbo].[USP_Get_xxx] 
	@qryType varchar(50), 
	@searchType varchar(50), 
	@searchText varchar(50), 
	@searchByCity varchar(50),  
	@advertiserId int
AS
SELECT 
	AR.* 
FROM 
	Tbl_RRRR AR  LEFT JOIN Tbl_PPP APS ON  
		AR.Id = APS. Id  
WHERE 
	(AR.Company_Name LIKE @searchText OR APS.Product_or_Service_Name   LIKE  @searchText)

Posted 15-Mar-11 4:59am

Manas Bhardwaj

Comments

Costica U 15-Mar-11 11:01am

My 5+.

Manas Bhardwaj 15-Mar-11 12:18pm

thnks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Accepted Answer · 2011-03-15T04:48:00

Solution 2

You must pass @searchText to sp_executesql

SQL

EXEC  sp_executesql @SQLQuery, N'@searchText varchar(50)', @searchText

Posted 15-Mar-11 4:48am

Costica U