从数据库汇总值并对其进行参数化。 [SQL Server] [英] Sum up values from databases and parametrize it. [SQL Server]
问题描述
我想总结来自多个数据库的值。此时我有三个数据库： SPA_PROD ， SPB_PROD 和 SPC_PROD 。
我的SQL查询：
如果EXISTS *
FROM sys.objects
WHERE object_id = OBJECT_ID（N'[dbo]。[TESTSUM]'）
和TYPE IN（N'P'，N'PC'））
DROP PROCEDURE [dbo]。[TESTSUM]
GO
CREATE PROC TESTSUM
AS
BEGIN
DECLARE @dbName SYSNAME，
@ObjectSUM INT，
@d datetime
SET @d ='20141113'
DECLARE @SQL NVARCHAR（MAX）
DECLARE @DBObjectStats TABLE（
--DBName SYSNAME，
DBObjects INT）
DECLARE curAllDBs CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE名称如'％PROD'
ORDER BY名称
打开curAllDBs
FETCH curAllDBs INTO @dbName
WHILE @FETCH_STATUS = 0） - db loop
BEGIN
- SQL QUERY
SET @SQL ='select @dbObjects = sum（doctotal）from'+
QuoteName ）+'..Invoice
其中DocDate ='''+ cast（@d as varchar（25））+'''''
PRINT @SQL - 调试
EXEC sp_executesql @SQL，N'@ dbObjects int output'，
@dbObjects = @ObjectSUM output
INSERT @DBObjectStats
SELECT @ObjecSUM
FETCH curAllDBs INTO @dbName
END
关闭curAllDBs
DEALLOCATE curAllDBs
- 返回结果
SELECT sum（DBObjects） [InvoiceSUM] FROM @DBObjectStats
END
GO
- 执行存储过程
EXEC TESTSUM
GO
这个工作完美，给我从所有的DB正确的总和：120 000 $（25 000从SPA_PROD，95 000 SPC_PROD和0从SPB_PROD。
我要做什么：
parametrize，它允许我选择日期和数据库。例如，我想选择 SPA_PROD 和 SPB_PROD 日期2014-01-01在另一种情况下，我想要所有的数据库 SPA + SPB + SPC 有另一个日期。
b
$ b
我可以使用SQL Server 2012和T-SQL的一切，也许这项技术可以提供最简单的方法。
我也使用SAP Crystal Reports将SQL输出转换成一个漂亮的报告。
对不起，我的英语和我试图向你描述我的问题， 。
使用你的例子我修改了它接受一串数据库名（通过你的水晶报表选择动作生成）。然后将带有问题的日期的此字符串传递到首先验证数据库是否存在以及是否在线将所需的union子句添加到生成的SQL代码。
CREATE PROCEDURE TESTSUM
@DbNameS NVARCHAR（max）
，@ Date DATETIME
AS
BEGIN
DECLARE @SQL NVARCHAR（MAX）=''
/ * ADD EXTRA'，'RO STRING ARRAY OF DATABASES * /
SET @DbNameS = @DbNameS +'，';
DECLARE @L INT = LEN（@DbNameS）;
DECLARE @D INT = 0;
DECLARE @LD INT = 1;
DECLARE @DBF VARCHAR（50）;
DECLARE @ACTIVE INT = 0;
/ * START SQL查询* /
SET @SQL ='SELECT SUM（[InvoiceSUM]）AS [InvoiceSUM] FROM（SELECT''''AS DB，0.00 AS [InvoiceSUM] '+ CHAR（13）
/ *通过每个DBF名称循环检查如果有效和在线*，$ /
WHILE @D< @L
BEGIN
SET @D = CHARINDEX（'，'，@ DbNameS，@ LD）;
IF @LD！= @D
BEGIN
SET @DBF = SUBSTRING（@ DbNameS，@ LD，@ D - @ LD）
/ * VALIDATE DBF有效和活动* /
SELECT @ACTIVE = COUNT（*）FROM SYS.databases WHERE name = @DBF AND [state] = 0
@ACTIVE = 1
BEGIN
/ *
BEGIN CODE TO UNION每个活动和有效DBF的总结果
使其与一些现有DBF一起使用我的系统我更改了用于测试的概要代码
* /
SET @SQL = @SQL +'UNION SELECT'''+ @DBF +'''AS DB，ISNULL（SUM ））），0）AS [InvoiceSUM] FROM'+ @DBF +'.DBO.SO_MSTR WHERE CAST（RecordCreated AS DATE）='''+ CAST（@Date AS VARCHAR（20））+'''' （13）
END;
END;
SET @LD = @D + 1;
END;
/ * CLOSE OUT UNION SUMMARY QUERY * /
SET @SQL = @SQL +'）AS DATA'
/ * OUTPUT RESULTS * /
EXEC SP_EXECUTESQL @ SQL
END;
Crystal报表可以生成此代码： EXEC TESTSUM'SPA_PROD，SPB_PROD ，SPC_PROD'，'12/09/2014'
I want to sum up values from several databases. At this moment I have three databases: SPA_PROD, SPB_PROD and SPC_PROD.
My SQL query:
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TESTSUM]')
AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE [dbo].[TESTSUM]
GO
CREATE PROC TESTSUM
AS
BEGIN
DECLARE @dbName SYSNAME,
@ObjectSUM INT,
@d datetime
SET @d = '20141113'
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBObjectStats TABLE (
--DBName SYSNAME,
DBObjects INT)
DECLARE curAllDBs CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name like '%PROD'
ORDER BY name
OPEN curAllDBs
FETCH curAllDBs INTO @dbName
WHILE (@@FETCH_STATUS = 0) -- db loop
BEGIN
--SQL QUERY
SET @SQL = 'select @dbObjects = sum(doctotal) from ' +
QuoteName(@dbName) + '..Invoice
where DocDate = ''' + cast(@d as varchar(25)) + ''''
PRINT @SQL -- Debugging
EXEC sp_executesql @SQL, N'@dbObjects int output',
@dbObjects = @ObjectSUM output
INSERT @DBObjectStats
SELECT @ObjecSUM
FETCH curAllDBs INTO @dbName
END
CLOSE curAllDBs
DEALLOCATE curAllDBs
-- Return results
SELECT sum(DBObjects) [InvoiceSUM] FROM @DBObjectStats
END
GO
-- Execute stored procedure
EXEC TESTSUM
GO
And this work perfect and giving me right sum from all my DBs: 120 000$ ( 25 000 from SPA_PROD , 95 000 SPC_PROD and 0 (NULL) from SPB_PROD.
What I want to do:
I would like to parametrize, which allows me to choose date and databases. For example I want to choose SPA_PROD and SPB_PROD with date 2014-01-01 in another case I want all databases (SPA + SPB + SPC with another date.
Is this even possible? Any ideas?
I can use everything what gives me SQL Server 2012 and T-SQL. Maybe this technology offers me easiest way to do this.
I am also using SAP Crystal Reports to convert SQL output into a beautiful report.
Sorry for my English and I tried to describe to you my problem as far as I could. If you want any additional information which helps u to help me -> ask me :).
Using your example I modified it to accept a string of database names (generated through you crystal reports select action). Then passing this string with the date in question to first validate the database exist and if online add the required union clause to the generated SQL code.
CREATE PROCEDURE TESTSUM
@DbNameS NVARCHAR(max)
,@Date DATETIME
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) = ''
/* ADD EXTRA ',' RO STRING ARRAY OF DATABASES */
SET @DbNameS = @DbNameS + ',';
DECLARE @L INT = LEN(@DbNameS);
DECLARE @D INT = 0;
DECLARE @LD INT = 1;
DECLARE @DBF VARCHAR(50);
DECLARE @ACTIVE INT = 0;
/* START SQL QUERY */
SET @SQL = 'SELECT SUM([InvoiceSUM]) AS [InvoiceSUM] FROM ( SELECT '''' AS DB, 0.00 AS [InvoiceSUM]' + CHAR(13)
/* LOOP THROUGH EACH DBF NAME PASSED CHECKING IF VALID AND ONLINE */
WHILE @D < @L
BEGIN
SET @D = CHARINDEX(',', @DbNameS,@LD);
IF @LD != @D
BEGIN
SET @DBF = SUBSTRING(@DbNameS,@LD,@D-@LD)
/* VALIDATE DBF IS VALID AND ACTIVE */
SELECT @ACTIVE = COUNT(*) FROM SYS.databases WHERE name = @DBF AND [state] = 0
IF @ACTIVE = 1
BEGIN
/*
BEGIN CODE TO UNION THE SUM RESULTS FOR EACH ACTIVE AND VALID DBF
TO MAKE IT WORK WITH SOME EXISTING DBF's ON MY SYSTEM I CHANGED THE SUMMARY CODE FOR TESTING
*/
SET @SQL = @SQL + 'UNION SELECT '''+ @DBF +''' AS DB, ISNULL(SUM( CAST(DVE AS DECIMAL(18,10)) ),0) AS [InvoiceSUM] FROM '+ @DBF + '.DBO.SO_MSTR WHERE CAST(RecordCreated AS DATE) = '''+ CAST(@Date AS VARCHAR(20)) + '''' + CHAR(13)
END;
END;
SET @LD = @D + 1;
END;
/* CLOSE OUT UNION SUMMARY QUERY */
SET @SQL = @SQL + ') AS DATA'
/* OUTPUT RESULTS */
EXEC SP_EXECUTESQL @SQL
END;
Crystal reports would effective be generating this code: EXEC TESTSUM 'SPA_PROD,SPB_PROD,SPC_PROD','12/09/2014'
这篇关于从数据库汇总值并对其进行参数化。 [SQL Server]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
