从数据库汇总值并对其进行参数化。 [SQL Server] | 从数据库汇总值并对其进行参数化

本文介绍了从数据库汇总值并对其进行参数化。 [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]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！