在sql过程中执行动态查询时出现错误 [英] getting error when executing dynamic query in sql procedure

问题描述

大家好，


我正在使用以下代码运行sql过程.

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 

我收到以下错误:必须声明标量变量"@searchText".

如果有人请提出建议.

预先谢谢您，
Varshini M.

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

If any one please suggest.

Thank You in advance,
Varshini M.

推荐答案

在下面尝试.

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 + '''''''' +  ) ''

搜索文本应在SQL语句中明确定义.它作为参数传递，因此您需要进行查询，以便将其值放在@SQLQuery变量中.

希望对您有所帮助.

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.

您为什么完全需要动态SQL?

Why do you need dynamic SQL at all?

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)

您必须将@searchText 传递给sp_executesql

You must pass @searchText to sp_executesql

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

这篇关于在sql过程中执行动态查询时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！