Problem Facing in Store procedure

Question

use [jabson]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_RptCategoryWiseSummary]

@FIELD_LIST nvarchar(Max),

@QUERY_FOR NVARCHAR(MAX),

@WHERE_CONDI nvarchar(max),

@GROUP_BY NVARCHAR(MAX),

@INDUSTRY_ID INT

AS

BEGIN

DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N''

DECLARE @UniqueOrderDateToPivot NVARCHAR(MAX) = N''

------ temporary commnet this dynamic portion ---------------------------------------------------------------------------------------

--DECLARE @GetUniqueList nvarchar(max) ='N SELECT DISTINCT' + @INNER_FIELD_NM + ' FROM dbo.SUPER_STOCKIST_MAS S,AREA_HEAD_MAS A,REGION_MAS R,ZONE_MAS Z, INDADMIN_MAS IA,INDUSTRY_MAS IM

-- WHERE S.AREA_HEAD_ID = A.ID AND A.REGION_ID = R.ID AND R.ZONE_ID = Z.ID AND Z.ADMIN_ID = IA.ID AND IA.INDUSTRY_ID= IM.ID

-- AND IM.ID=2 '

-----------------------------------------------------------------------------------------------------------------------------------

SELECT @UniqueOrderDateToPivot = @UniqueOrderDateToPivot + ',[' + COALESCE(ORDER_DATE,'') + ']' FROM (SELECT DISTINCT CONVERT(NVARCHAR(MAX),OM.ORDER_DATE,112) AS ORDER_DATE FROM ORDER_MAS OM , ORDER_DET OD WHERE OM.ID = OD.ORDER_ID) dt

IF(CHARINDEX(@QUERY_FOR,@UniqueOrderDateToPivot) > 0)

BEGIN

SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(FULL_NAME, '') + ']' FROM ( SELECT DISTINCT Z.FULL_NAME FROM DBO.SUPER_STOCKIST_MAS S,AREA_HEAD_MAS A,REGION_MAS R,ZONE_MAS Z, INDADMIN_MAS IA,INDUSTRY_MAS IM,BUDGET_TARGET_MAS BTM

WHERE S.AREA_HEAD_ID = A.ID AND A.REGION_ID = R.ID AND R.ZONE_ID = Z.ID AND Z.ADMIN_ID = IA.ID AND IA.INDUSTRY_ID= IM.ID

AND IM.ID=@INDUSTRY_ID )DT

END

ELSE IF (CHARINDEX(@QUERY_FOR,@UniqueOrderDateToPivot) > 0)

BEGIN

SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(FULL_NAME, '') + ']' FROM ( SELECT DISTINCT R.FULL_NAME FROM DBO.SUPER_STOCKIST_MAS S,AREA_HEAD_MAS A,REGION_MAS R,ZONE_MAS Z, INDADMIN_MAS IA,INDUSTRY_MAS IM

WHERE S.AREA_HEAD_ID = A.ID AND A.REGION_ID = R.ID AND R.ZONE_ID = Z.ID AND Z.ADMIN_ID = IA.ID AND IA.INDUSTRY_ID= IM.ID

AND IM.ID=@INDUSTRY_ID )DT

END

ELSE IF (CHARINDEX(@QUERY_FOR,@UniqueOrderDateToPivot) > 0)

BEGIN

SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(FULL_NAME, '') + ']' FROM ( SELECT DISTINCT A.FULL_NAME FROM DBO.SUPER_STOCKIST_MAS S,AREA_HEAD_MAS A,REGION_MAS R,ZONE_MAS Z, INDADMIN_MAS IA,INDUSTRY_MAS IM

WHERE S.AREA_HEAD_ID = A.ID AND A.REGION_ID = R.ID AND R.ZONE_ID = Z.ID AND Z.ADMIN_ID = IA.ID AND IA.INDUSTRY_ID= IM.ID

AND IM.ID=@INDUSTRY_ID )DT

END

ELSE IF (CHARINDEX(@QUERY_FOR,@UniqueOrderDateToPivot) > 0)

BEGIN

SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(FULL_NAME, '') + ']' FROM ( SELECT DISTINCT S.FULL_NAME FROM DBO.SUPER_STOCKIST_MAS S,AREA_HEAD_MAS A,REGION_MAS R,ZONE_MAS Z, INDADMIN_MAS IA,INDUSTRY_MAS IM

WHERE S.AREA_HEAD_ID = A.ID AND A.REGION_ID = R.ID AND R.ZONE_ID = Z.ID AND Z.ADMIN_ID = IA.ID AND IA.INDUSTRY_ID= IM.ID

AND IM.ID=@INDUSTRY_ID )DT

END

SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space

DECLARE @SQLInnerQuery nvarchar(max) = N'

SELECT C.ID,C.CATEGORY_NM

'+@FIELD_LIST +'

,SUM(CONVERT(DECIMAL(18,0),OD.QTY)) AS QTY

FROM

ORDER_MAS OM,ORDER_DET OD,PRODUCT_MAS P,CATEGORY_MAS C,INDUSTRY_MAS IM,INDADMIN_MAS IAM, BUDGET_TARGET_MAS BTM, PRODUCT_TARGET_MAS PTM

,ZONE_MAS Z,REGION_MAS R,AREA_HEAD_MAS A,SUPER_STOCKIST_MAS S

WHERE OM.ID=OD.ORDER_ID AND P.ID=OD.PRO_ID AND P.CID= C.ID AND IM.ID= OM.INDUSTRY_ID AND IAM.INDUSTRY_ID = IM.ID

AND Z.ADMIN_ID= IAM.ID

AND R.ZONE_ID = Z.ID

AND A.REGION_ID = R.ID

AND S.AREA_HEAD_ID = A.ID

AND OM.SID = S.ID

AND BTM.AREA_HEAD_ID = A.ID

AND BTM.AREA_HEAD_ID = PTM.AREA_HEAD_ID

AND PTM.BUDGET_ID = BTM.ID

AND OD.PRO_ID = PTM.PRO_ID

' + @WHERE_CONDI + ' GROUP BY CATEGORY_NM,C.ID' + @GROUP_BY

DECLARE @SQLStatement NVARCHAR(max)

SET @SQLStatement= N'

WITH CATEGORY_NM AS (

' + ISNULL(@SQLInnerQuery,'') + '

)

SELECT *

FROM

CATEGORY_NM

PIVOT (SUM(QTY) FOR FULL_NAME IN

('+ ISNULL(@UniqueCustomersToPivot,'') +')

) P ORDER BY ID '

EXEC (@SQLStatement)

END

i am trying this store procedure but i am not getting any result

can you please help to solve quotes errore for execute this store procedure

----------