1

I have a table called procedure look up which stores medical procedures and have multiple company table for which i had to calculate the procedure fees so i had created a dynamic query for it

below is the query

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

but when executing error occurs saying "Incorrect syntax near '@Name1'"

can anyone help me with that where condition side issue

Kiran M R
  • 91
  • 7

1 Answers1

2

I think It may have something to do with your like statement and the way you pass the parameter.

Have a look at this question Parameters & Like statement. 

@Name1 = "'%yourvalue%'"
Community
  • 1
  • 1
ojhawkins
  • 3,200
  • 15
  • 50
  • 67
  • 1
    ya but can you please suggest a method in which i can use the like statement – Kiran M R Aug 25 '13 at 12:40
  • 1
    Try `@Name1 = "'%yourvalue%'"` When `@Name1` is resolved it will be in the correct format for the `like` operator. I am guessing you will need to do the same for `@Code1` @kevinkiran – ojhawkins Aug 25 '13 at 12:45
  • 1
    i found a solution for this, i used a table variable to store data that i got from the dynamic query and then used this table variable to join with other tables to get the result. Thank u all for giving the options – Kiran M R Sep 08 '13 at 05:24