调用服务端命令SQL ，数据库能跑出数据但，用在活字格里面就跑不出来

1. DECLARE @query NVARCHAR(MAX),@Salesmen varchar(200)
   
2. 
   
3. set @Salesmen= '""生庆禹","任宇航","侯俊帅","马聪","魏军","刘亚伟","钱正伦","许丹娜","田一傲雪","周鹏","于勇林"" '
   
4. 
   
5. 
   
6. 
   
7. SET @Salesmen = REPLACE(@Salesmen, '"', '');
   
8. 
   
9. 
   
10. 
    
11. --SELECT '@Salesmen=' + QUOTENAME(@Salesmen, '"');
    
12. 
    
13. SET @Salesmen = REPLACE(@Salesmen, '"', '')
    
14. 
    
15. SET @Salesmen = REPLACE(@Salesmen, ',', ''',''')
    
16. 
    
17. SET @query = '
    
18. 
    
19. SELECT
    
20. 
    
21.     业务员,
    
22. 
    
23.     年,
    
24. 
    
25.     SUM([1月]) as ''1月'',
    
26. 
    
27.     SUM([2月]) as ''2月'',
    
28. 
    
29.     SUM([3月]) as ''3月'',
    
30. 
    
31.     SUM([4月]) as ''4月'',
    
32. 
    
33.     SUM([5月]) as ''5月'',
    
34. 
    
35.     SUM([6月]) as ''6月'',
    
36. 
    
37.     SUM([7月]) as ''7月'',
    
38. 
    
39.     SUM([8月]) as ''8月'',
    
40. 
    
41.     SUM([9月]) as ''9月'',
    
42. 
    
43.     SUM([10月]) as ''10月'',
    
44. 
    
45.     SUM([11月]) as ''11月'',
    
46. 
    
47.     SUM([12月]) as ''12月''
    
48. 
    
49. FROM (
    
50. 
    
51.     SELECT
    
52. 
    
53.         CASE
    
54. 
    
55.             WHEN NameOfTrueOrg =''大连桥口贸易有限公司-任宇航（青岛）'' THEN ''任宇航''
    
56. 
    
57.             ELSE SUBSTRING(NameOfTrueOrg, CHARINDEX(''-'', NameOfTrueOrg) + 1, LEN(NameOfTrueOrg))  
    
58. 
    
59.         END as 业务员,
    
60. 
    
61.         YEAR(CreateTime) as 年,
    
62. 
    
63.         SUM(CASE WHEN MONTH(CreateTime) = 1 THEN NeedCharges ELSE 0 END) as ''1月'',
    
64. 
    
65.         SUM(CASE WHEN MONTH(CreateTime) = 2 THEN NeedCharges ELSE 0 END) as ''2月'',
    
66. 
    
67.         SUM(CASE WHEN MONTH(CreateTime) = 3 THEN NeedCharges ELSE 0 END) as ''3月'',
    
68. 
    
69.         SUM(CASE WHEN MONTH(CreateTime) = 4 THEN NeedCharges ELSE 0 END) as ''4月'',
    
70. 
    
71.         SUM(CASE WHEN MONTH(CreateTime) = 5 THEN NeedCharges ELSE 0 END) as ''5月'',
    
72. 
    
73.         SUM(CASE WHEN MONTH(CreateTime) = 6 THEN NeedCharges ELSE 0 END) as ''6月'',
    
74. 
    
75.         SUM(CASE WHEN MONTH(CreateTime) = 7 THEN NeedCharges ELSE 0 END) as ''7月'',
    
76. 
    
77.         SUM(CASE WHEN MONTH(CreateTime) = 8 THEN NeedCharges ELSE 0 END) as ''8月'',
    
78. 
    
79.         SUM(CASE WHEN MONTH(CreateTime) = 9 THEN NeedCharges ELSE 0 END) as ''9月'',
    
80. 
    
81.         SUM(CASE WHEN MONTH(CreateTime) = 10 THEN NeedCharges ELSE 0 END) as ''10月'',
    
82. 
    
83.         SUM(CASE WHEN MONTH(CreateTime) = 11 THEN NeedCharges ELSE 0 END) as ''11月'',
    
84. 
    
85.         SUM(CASE WHEN MONTH(CreateTime) = 12 THEN NeedCharges ELSE 0 END) as ''12月''
    
86. 
    
87.     FROM T_Biz_Task
    
88. 
    
89.     WHERE NameOfTrueOrg in (''北京京检实力可技术检测有限公司-生庆禹'',''大连桥口贸易有限公司-任宇航（青岛）'',''梅里埃检测技术（北京）有限公司-侯俊帅'',''梅里埃检测技术（北京）有限公司-马聪'',''梅里埃检测技术（北京）有限公司-魏军'',''梅里埃检测技术(宁波)有限公司-刘亚伟'',''梅里埃检测技术(宁波)有限公司-钱正伦 '',''梅里埃检测技术（宁波）有限公司-许丹娜'',''梅里埃检测技术(青岛)有限公司-田一傲雪'',''梅里埃检测技术(青岛)有限公司-任宇航'')
    
90. 
    
91.     GROUP BY NameOfTrueOrg, YEAR(CreateTime)
    
92. 
    
93. 
    
94. 
    
95.     UNION ALL
    
96. 
    
97. 
    
98. 
    
99.     SELECT
    
100. 
     
101.         Salesman as 业务员,
     
102. 
     
103.         YEAR(CreateTime) as 年,
     
104. 
     
105.         SUM(CASE WHEN MONTH(CreateTime) = 1 THEN NeedCharges ELSE 0 END) as ''1月'',
     
106. 
     
107.         SUM(CASE WHEN MONTH(CreateTime) = 2 THEN NeedCharges ELSE 0 END) as ''2月'',
     
108. 
     
109.         SUM(CASE WHEN MONTH(CreateTime) = 3 THEN NeedCharges ELSE 0 END) as ''3月'',
     
110. 
     
111.         SUM(CASE WHEN MONTH(CreateTime) = 4 THEN NeedCharges ELSE 0 END) as ''4月'',
     
112. 
     
113.         SUM(CASE WHEN MONTH(CreateTime) = 5 THEN NeedCharges ELSE 0 END) as ''5月'',
     
114. 
     
115.         SUM(CASE WHEN MONTH(CreateTime) = 6 THEN NeedCharges ELSE 0 END) as ''6月'',
     
116. 
     
117.         SUM(CASE WHEN MONTH(CreateTime) = 7 THEN NeedCharges ELSE 0 END) as ''7月'',
     
118. 
     
119.         SUM(CASE WHEN MONTH(CreateTime) = 8 THEN NeedCharges ELSE 0 END) as ''8月'',
     
120. 
     
121.         SUM(CASE WHEN MONTH(CreateTime) = 9 THEN NeedCharges ELSE 0 END) as ''9月'',
     
122. 
     
123.         SUM(CASE WHEN MONTH(CreateTime) = 10 THEN NeedCharges ELSE 0 END) as ''10月'',
     
124. 
     
125.         SUM(CASE WHEN MONTH(CreateTime) = 11 THEN NeedCharges ELSE 0 END) as ''11月'',
     
126. 
     
127.         SUM(CASE WHEN MONTH(CreateTime) = 12 THEN NeedCharges ELSE 0 END) as ''12月''
     
128. 
     
129.     FROM T_Biz_Task
     
130. 
     
131.     WHERE Salesman in (''' + @Salesmen + ''')
     
132. 
     
133.     GROUP BY Salesman, YEAR(CreateTime)
     
134. 
     
135. ) combined_data
     
136. 
     
137. GROUP BY 业务员, 年
     
138. 
     
139. ORDER BY 业务员, 年';
     
140. 
     
141. 
     
142. 
     
143. EXEC sp_executesql @query;

复制代码

SQL代码是这样的   ，活字格里面只是把  @Salesmen 的值通过页面上的文本框获取

查询出来只有我写死了SQL的业务员的东西，通过传值去查询的东西没有   

您好，执行 SQL 命令的参数 IN 还传递不进去哈。已经记录了需求。

您可以参考下面这个帖子来解决：

服务端命令执行sql 参数中的值不能是多个吗
https://gcdn.grapecity.com.cn/showtopic-138810-1-1.html
(出处: 葡萄城开发者社区)