Try this and let us know
USE [ABC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[LibFetchFilterItem]
@Query nvarchar(1000)
AS
Begin
set nocount on
DECLARE @str varchar(8000)
SET @str = N'
select ID
,case when i.CodeVersion>1 then i.Code+''+'''.'''+''+convert(varchar,i.CodeVersion) else i.Code end as Code
,i.OldCode
,HRMS.dbo.InitialCap(i.[Name]) as Name
,i.[Description]
,d.DeptName as Department
,HRMS.dbo.InitialCap(p.[Name]) as Product
,Edition
,[Version]
,PublisherBrand
,Country
,KeywordDesc
,l.[name] as Location
,HRMS.dbo.InitialCap(s.[Name]) as SubCategory
,Synopsis
,i.Website
from LIBMstItem i inner join LIBMstLocation l on i.LocationId=l.LocationId
inner join LIBMstProduct p on i.ProductCategory = p.Id
Left outer join HRMS.dbo.Master_EmployeeMaster u on i.BroughtBy=u.[NewID]
Left outer join dynamicreport.dbo.CompanyMst c on i.CompanyId = c.CompanyId
Left outer join HRMS.dbo.Master_DepartmentMaster d on i.DepartmentID = d.RowID
inner join LIBMstSubCategory s on i.SubCategory = s.Id
where (i.Scrap is null or i.Scrap=0)
and i.IsActive=1
and i.KitId=0' + ' ' + @Query + ' ' +
'and 0=0 order by i.ID'
PRINT @str
EXEC @str
end
go
I hope it will solve your problem