[MSSQL] 동적쿼리를 이용한 프로시저

프로시저 소스

1. ALTER PROCEDURE SP_SQUTY12PL_WITH_OLD_000
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. , @area nvarchar(2)
5. , @agent nvarchar(10)
6. , @bokjong nvarchar(8)
7. AS
8. BEGIN
9. DECLARE
10. @sql nvarchar(4000)
11. , @param nvarchar(400)
12. , @yyyyMinus1 nvarchar(8)
13. , @yyMinus1 nvarchar(4)
14. , @yyMinus2 nvarchar(4)
15. , @yyMinus3 nvarchar(4)
16. , @yySeason nvarchar(6)
17. set @param = '@p_year nvarchar(8)
18. , @p_season nvarchar(2)
19. , @p_area nvarchar(2)
20. , @p_agent nvarchar(2)
21. , @p_yyyyMinus1 nvarchar(8)
22. , @p_yyMinus1 nvarchar(4)
23. , @p_yyMinus2 nvarchar(4)
24. , @p_yyMinus3 nvarchar(4)
25. , @p_yySeason nvarchar(6)
26. , @p_bokjong nvarchar(8)
27. '
28. set @yySeason = substring(@year, 3, 2) + @season
29. set @yyyyMinus1 = convert(nvarchar(4), @year - 1)
30. set @yyMinus1 = convert(nvarchar(4), substring(@year, 3, 2) - 1)
31. set @yyMinus2 = convert(nvarchar(4), substring(@year, 3, 2) - 2)
32. set @yyMinus3 = convert(nvarchar(4), substring(@year, 3, 2) - 3)
33. set @sql = ' SELECT
34. T1.AREA
35. , ISNULL(SUJU_QTY, 0) SUJU_QTY, ISNULL(SUJU_AMT, 0) SUJU_AMT
36. , ISNULL(CHGO_QTY, 0) CHGO_QTY, ISNULL(CHGO_AMT, 0) CHGO_AMT
37. , ISNULL(INVT_QTY, 0) INVT_QTY, ISNULL(INVT_AMT, 0) INVT_AMT
38. , ISNULL(CHGO_QTY, 0) - ISNULL(INVT_QTY, 0) SALE_QTY
39. , ISNULL(CHGO_AMT, 0) - ISNULL(INVT_AMT, 0) SALE_AMT
40. FROM (
41. SELECT * FROM (
42. SELECT CODE AS AREA, SERIAL FROM MCODE02TT WHERE GRCODE = ''101''
43. ) T1
44. ) T1 LEFT OUTER JOIN (
45. SELECT
46. AREA, SUM(T1.SUJU_QTY) SUJU_QTY, SUM(T1.SUJU_AMT) SUJU_AMT
47. FROM (
48. SELECT 
49. T1.AREA, T1.ORDERNO, T1.SUJU_QTY
50. , T1.SUJU_QTY * ISNULL((SELECT CHULGOGA CHULGOGA FROM SCHGO01TT X WHERE X.SEASON=T1.SEASON AND X.PUMBUN = T1.PUMBUN),0) SUJU_AMT
51. FROM SMAST01TT T1 INNER JOIN SQUTY01TT T2
52. ON T1.ORDERNO = T2.ORDERNO
53. WHERE T2.QTY_GUBUN = ''1''
54. AND SEASON = @p_yySeason '
55. if @bokjong is not null or @bokjong <> ''
56. begin
57. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
58. end
59. set @sql = @sql + ') T1
60. GROUP BY AREA
61. ) T2 ON T1.AREA = T2.AREA
62. LEFT OUTER JOIN (
63. SELECT 
64. AREA, SUM(CHGO_QTY) AS CHGO_QTY, SUM(CHGO_QTY * CHGO_AMT) CHGO_AMT
65. FROM (
66. SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS CHGO_QTY
67. , ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG  WHERE X.SEASON = @p_yySeason),0) AS CHGO_AMT 
68. FROM SINVT01TT T1
69. WHERE YEAR = @p_yyyyMinus1 AND SEASON = @p_season AND S_YEAR IN (@p_yyMinus1, @p_yyMinus2, @p_yyMinus3) '
70. /* AND T1.BOKJONG = ''C'' */
71. if @bokjong is not null or @bokjong <> ''
72. begin
73. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
74. end
75. set @sql = @sql + ') T1
76. GROUP BY AREA
77. ) T3
78. ON T1.AREA = T3.AREA
79. LEFT OUTER JOIN (
80. SELECT 
81. AREA, SUM(INVT_QTY) AS INVT_QTY, SUM(INVT_QTY * INVT_AMT) INVT_AMT
82. FROM (
83. SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS INVT_QTY
84. , ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG  WHERE X.SEASON = @p_yySeason),0) AS INVT_AMT 
85. FROM SINVT01TT T1
86. WHERE YEAR = @p_year AND SEASON = @p_season AND S_YEAR IN (substring(@p_year, 3, 2), @p_yyMinus1, @p_yyMinus2) '
87. /* AND T1.BOKJONG = ''C'' */
88. if @bokjong is not null or @bokjong <> ''
89. begin
90. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
91. end
92. set @sql = @sql + ') T1
93. GROUP BY AREA
94. ) T4
95. ON T1.AREA = T4.AREA
96. ORDER BY SERIAL
97. '
98. exec SP_EXECUTESQL @sql, @param, @p_year=@year, @p_season=@season, @p_area=@area, @p_agent=@agent, @p_yyyyMinus1=@yyyyMinus1, @p_yyMinus1=@yyMinus1, @p_yyMinus2=@yyMinus2, @p_yyMinus3=@yyMinus3, @p_yySeason=@yySeason, @p_bokjong=@bokjong    
99. END
100. 
     

테스트

1. USE [skoolooks_erp]
   GO
   
   
   DECLARE@return_value int
   
   
   EXEC@return_value = [dbo].[SP_SQUTY12PL_WITH_OLD_000]
   @year = N'2012',
   @season = N'F',
   @area = null,
   @agent = null,
   @bokjong = null
   
   
   SELECT'Return Value' = @return_value
   
   
   GO