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

登录

DECLARE @query NVARCHAR(MAX),@Salesmen varchar(200)
set @Salesmen= '""生庆禹","任宇航","侯俊帅","马聪","魏军","刘亚伟","钱正伦","许丹娜","田一傲雪","周鹏","于勇林"" '
SET @Salesmen = REPLACE(@Salesmen, '"', '');
--SELECT '@Salesmen=' + QUOTENAME(@Salesmen, '"');
SET @Salesmen = REPLACE(@Salesmen, '"', '')
SET @Salesmen = REPLACE(@Salesmen, ',', ''',''')
SET @query = '
SELECT
业务员,
年,
SUM([1月]) as ''1月'',
SUM([2月]) as ''2月'',
SUM([3月]) as ''3月'',
SUM([4月]) as ''4月'',
SUM([5月]) as ''5月'',
SUM([6月]) as ''6月'',
SUM([7月]) as ''7月'',
SUM([8月]) as ''8月'',
SUM([9月]) as ''9月'',
SUM([10月]) as ''10月'',
SUM([11月]) as ''11月'',
SUM([12月]) as ''12月''
FROM (
SELECT
CASE
WHEN NameOfTrueOrg =''大连桥口贸易有限公司-任宇航（青岛）'' THEN ''任宇航''
ELSE SUBSTRING(NameOfTrueOrg, CHARINDEX(''-'', NameOfTrueOrg) + 1, LEN(NameOfTrueOrg))
END as 业务员,
YEAR(CreateTime) as 年,
SUM(CASE WHEN MONTH(CreateTime) = 1 THEN NeedCharges ELSE 0 END) as ''1月'',
SUM(CASE WHEN MONTH(CreateTime) = 2 THEN NeedCharges ELSE 0 END) as ''2月'',
SUM(CASE WHEN MONTH(CreateTime) = 3 THEN NeedCharges ELSE 0 END) as ''3月'',
SUM(CASE WHEN MONTH(CreateTime) = 4 THEN NeedCharges ELSE 0 END) as ''4月'',
SUM(CASE WHEN MONTH(CreateTime) = 5 THEN NeedCharges ELSE 0 END) as ''5月'',
SUM(CASE WHEN MONTH(CreateTime) = 6 THEN NeedCharges ELSE 0 END) as ''6月'',
SUM(CASE WHEN MONTH(CreateTime) = 7 THEN NeedCharges ELSE 0 END) as ''7月'',
SUM(CASE WHEN MONTH(CreateTime) = 8 THEN NeedCharges ELSE 0 END) as ''8月'',
SUM(CASE WHEN MONTH(CreateTime) = 9 THEN NeedCharges ELSE 0 END) as ''9月'',
SUM(CASE WHEN MONTH(CreateTime) = 10 THEN NeedCharges ELSE 0 END) as ''10月'',
SUM(CASE WHEN MONTH(CreateTime) = 11 THEN NeedCharges ELSE 0 END) as ''11月'',
SUM(CASE WHEN MONTH(CreateTime) = 12 THEN NeedCharges ELSE 0 END) as ''12月''
FROM T_Biz_Task
WHERE NameOfTrueOrg in (''北京京检实力可技术检测有限公司-生庆禹'',''大连桥口贸易有限公司-任宇航（青岛）'',''梅里埃检测技术（北京）有限公司-侯俊帅'',''梅里埃检测技术（北京）有限公司-马聪'',''梅里埃检测技术（北京）有限公司-魏军'',''梅里埃检测技术(宁波)有限公司-刘亚伟'',''梅里埃检测技术(宁波)有限公司-钱正伦 '',''梅里埃检测技术（宁波）有限公司-许丹娜'',''梅里埃检测技术(青岛)有限公司-田一傲雪'',''梅里埃检测技术(青岛)有限公司-任宇航'')
GROUP BY NameOfTrueOrg, YEAR(CreateTime)
UNION ALL
SELECT
Salesman as 业务员,
YEAR(CreateTime) as 年,
SUM(CASE WHEN MONTH(CreateTime) = 1 THEN NeedCharges ELSE 0 END) as ''1月'',
SUM(CASE WHEN MONTH(CreateTime) = 2 THEN NeedCharges ELSE 0 END) as ''2月'',
SUM(CASE WHEN MONTH(CreateTime) = 3 THEN NeedCharges ELSE 0 END) as ''3月'',
SUM(CASE WHEN MONTH(CreateTime) = 4 THEN NeedCharges ELSE 0 END) as ''4月'',
SUM(CASE WHEN MONTH(CreateTime) = 5 THEN NeedCharges ELSE 0 END) as ''5月'',
SUM(CASE WHEN MONTH(CreateTime) = 6 THEN NeedCharges ELSE 0 END) as ''6月'',
SUM(CASE WHEN MONTH(CreateTime) = 7 THEN NeedCharges ELSE 0 END) as ''7月'',
SUM(CASE WHEN MONTH(CreateTime) = 8 THEN NeedCharges ELSE 0 END) as ''8月'',
SUM(CASE WHEN MONTH(CreateTime) = 9 THEN NeedCharges ELSE 0 END) as ''9月'',
SUM(CASE WHEN MONTH(CreateTime) = 10 THEN NeedCharges ELSE 0 END) as ''10月'',
SUM(CASE WHEN MONTH(CreateTime) = 11 THEN NeedCharges ELSE 0 END) as ''11月'',
SUM(CASE WHEN MONTH(CreateTime) = 12 THEN NeedCharges ELSE 0 END) as ''12月''
FROM T_Biz_Task
WHERE Salesman in (''' + @Salesmen + ''')
GROUP BY Salesman, YEAR(CreateTime)
) combined_data
GROUP BY 业务员, 年
ORDER BY 业务员, 年';
EXEC sp_executesql @query;

复制代码

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

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

Simon.Sun · Simon.Sun

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

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

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

		自动登录	找回密码
密码			立即注册

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

最佳答案

1 个回复

Wyn高级认证

Wyn认证

活字格认证

活字格高级认证