在SQL server 创建了一个存储过程，如何在Foxtable中调用生成datatable，帮助看不太懂ALTER PROCEDURE [dbo].[GenerateDynamicPIVOT]
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX), @caseCols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SE/ECT @cols = STUFF(
(SE/ECT ', ' + QUOTENAME(店铺自定义科目二)
FROM 店铺自定义科目档案
WHERE 店铺自定义科目二 IS NOT NULL
ORDER BY 店铺自定义科目二
FOR XML PATH('')),
1, 2, ''
);
SE/ECT @caseCols = STUFF(
(SE/ECT ', ISNULL(SUM(CASE WHEN 店铺自定义科目二 = ''' + 店铺自定义科目二 + ''' THEN 借方金额 ELSE 0 END), 0) AS ' + QUOTENAME(店铺自定义科目二)
FROM 店铺自定义科目档案
WHERE 店铺自定义科目二 IS NOT NULL
ORDER BY 店铺自定义科目二
FOR XML PATH('')),
1, 2, ''
);
SET @query = '
SE/ECT
年份,月份,部门名称,事业部,区经,
' + @caseCols + '
FROM FF店铺费用汇总表
GROUP BY 年份,月份,部门名称,事业部,区经
ORDER BY 年份,月份,部门名称,事业部,区经';
PRINT @query;
EXEC sp_executesql @query;
END
http://www.foxtable.com/webhelp/topics/3267.htm
Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "GenerateDynamicPIVOT"
cmd.StoredProcedure = True
Dim dt As DataTable = cmd.ExecuteReader '获取订单生成DataTable