视图如何改为临时表+sp?
现有一视图代码如下:
CREATE VIEW dbo.v_stock_month
AS
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size)
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
主要用来查询某月每天的出入库台帐。
考虑到需要带年度和月份做参数,想改用sp+临时表实现,以提高性能和灵活性。
请各位大虾给个例子。