22,297
社区成员




ALTER PROCEDURE [dbo].[facst_2024_01_Filter_Product_Type]
@customer VARCHAR(MAX)
AS
BEGIN
DECLARE @Filter_product_type VARCHAR(MAX), @YM VARCHAR(MAX),@WHERE_product_type VARCHAR(MAX);
DECLARE @WHERETOP1 VARCHAR(MAX), @WHERETOP2 VARCHAR(MAX), @WHERETOP3 VARCHAR(MAX);
DECLARE @versions1 VARCHAR(MAX), @versions2 VARCHAR(MAX), @versions3 VARCHAR(MAX);
DECLARE @TOP1 VARCHAR(MAX), @TOP2 VARCHAR(MAX);
SET @TOP1 = '1';
SET @TOP2 = '2';
SET @versions1 =
(
SELECT TOP 3 versions
FROM
(
SELECT versions,
ROW_NUMBER() OVER(
ORDER BY versions DESC) no
FROM [RS_SYS002].[dbo].[V_FCAST_TEST]
GROUP BY versions
) A
WHERE A.no = @TOP1
);
SET @versions2 =
(
SELECT TOP 3 versions
FROM
(
SELECT versions,
ROW_NUMBER() OVER(
ORDER BY versions DESC) no
FROM [RS_SYS002].[dbo].[V_FCAST_TEST]
GROUP BY versions
) A
WHERE A.no = @TOP2
);
SET @WHERETOP1 = 'versions=' + @versions1;
SET @WHERETOP2 = 'versions=' + @versions2;
SET @WHERETOP3 = 'versions=''20231130''' ;
SET @WHERE_product_type = 'table0.customer in (@customer)'
SET @YM =
(
SELECT MIN(YM) ym
FROM [V_FCAST_TEST] A
WHERE A.versions = @versions1
GROUP BY A.versions
);
SET @Filter_product_type ='SELECT DISTINCT (table0.product_type)product_type
FROM
(
SELECT 1 AS Order_by_ID,
[Customer],
[Product_Type],
[Sales]
FROM [RS_SYS002].[dbo].[V_FCAST_TEST]
WHERE versions = ''' + @versions1 + '''
UNION
SELECT 1 AS Order_by_ID,
[Customer],
[Product_Type],
[Sales]
FROM [RS_SYS002].[dbo].[V_FCAST_TEST]
WHERE versions = ''' + @versions2 + '''
UNION
SELECT 1 AS Order_by_ID,
[Customer],
[Product_Type],
[Sales]
FROM [RS_SYS002].[dbo].[V_FCAST_TEST]
WHERE versions = ''' + @versions3 + '''
) TABLE0
where ' + @WHERE_product_type + ''
EXEC (@Filter_product_type)
END
@customer 想用reporting services传字串赋值。目前提示必须声明变量@customer,不知道哪里出错,请各位大佬指教一下