Sum up values from databases and parametrize it. [

问题:

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 :).

回答1:

You can create a User-Defined Table Type:

CREATE TYPE DBTable AS TABLE 
(
     DBName VARCHAR(128)
);

You can use it as an input parameter of your stored procedure. As well as the date parameter.

CREATE PROCEDURE TESTSUM
    @Databases DBTable READONLY
   ,@Date DATETIME
AS
BEGIN
...
...
... 

You call it like this:

DECLARE @T AS DBTable;
DECLARE @D AS DATETIME = GETDATE();
INSERT INTO @T VALUES ('DB1', 'DB2', 'DB3')
EXEC TESTSUM @T, @D

回答2:

maybe instead of

SELECT name
       FROM MASTER.dbo.sysdatabases

use

SELECT name
       FROM #temptable

and insert into #temptable specific db you want

回答3:

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'