动态SQL查询不起作用
动态SQL查询不起作用
我有一张名为"procedure look up"的表,存储着医疗程序,并且有多个公司表,我需要计算这些程序的费用,所以我创建了一个动态查询。以下是查询的内容:
declare @TableProviderName varchar(500) ,@SQLQuery1 nvarchar(max) ,@MaxRecordSize Int ,@Name varchar(250) = null ,@code varchar(50) = null set @Name = 'sug' set @TableProviderName = 'PRD_Tata_Details' set @MaxRecordSize = 50 set @SQLQuery1 = ' ;WITH CTE_Procedure AS ( select top (@MaxRecordSize1) GPL_ID_PK as ProcedureID ,GPL_ProcedureType as ProcedureType ,GPL_Code as ProcedureCode ,coalesce(Name,GPL_Name,null)as Procedurename ,GPL_CurrencyType_FK as CurrencyType ,ISNULL(GPL_Description,''NIL'') as ProcedureDescription ,ISNULL(GPL_PatientInstruction,''NIL'')as PatientInstructions ,GPL_ProcedureCategory_FK as ProcedureCategory ,GPL_CategorySpecialization_FK as ProcedureSpecialization ,coalesce(PatientPayable,GPL_ProcedureFee,0) as PatientPayable ,0 as InsurancePayable ,0 as InsuranceDiscount ,1 as ProcedureCount ,0 as IndBillingStatus ,Case when GeneralProcedureID is not null then ''Insurance Supported'' else ''Insurance not Supported'' end as InsuranceStatus ,ROW_NUMBER( ) OVER ( ORDER BY GPL_Name ASC) as RowNumber from dbo.PRD_GeneralProcedure_Lookup left join ' + @TableProviderName + ' on GeneralProcedureID = GPL_ID_PK where GPL_ProcedureType = @ProcedureType1 and (@Name1 is null or GPL_Name like %@Name1%) and (@code1 is null or GPL_Code like %@code1%) ) Select * from CTE_Procedure ' Execute sp_executesql @SQLQuery1, N'@MaxRecordSize1 int, @ProcedureType1 tinyint,@Name1 varchar(250) , @code varchar(50)' ,@MaxRecordSize1 = @MaxRecordSize, @ProcedureType1 = 1 , @Name1 = @Name, @code1 = @code
但是在执行时出现错误,显示"在'@Name1'处语法错误"。有谁能帮我解决这个条件语句的问题?
动态SQL查询不起作用的原因可能与like
语句和参数传递方法有关。请参考这个问题:Parameters & Like statement。尝试使用 = "'%yourvalue%'"
,当被解析时,它将以正确的格式传递给
like
运算符。我猜想你可能需要对进行相同的处理。
我找到了一个解决方法,我使用了一个表变量来存储从动态查询中获取的数据,然后使用这个表变量与其他表进行连接以获取结果。感谢大家提供的选项。