Latif Diwan

Latif Diwan

  • 1.9k
  • 5
  • 61

Problem Facing in Store procedure

Jan 22 2020 5:25 PM
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
----------

Answers (1)