CODEWITHSUNDEEP
sqlsql-serverstored-proceduresnested
BraKi Brakstad
BraKi Brakstad

Reputation: 21

SQL: How can i run nested Stored Procedures and return multiple values

I've been trying to run a procedure like this:

ALTER procedure [dbo].[GetViewData]
AS
IF EXISTS( SELECT * FROM dbo.CONFIG )
BEGIN
    DECLARE @count as INTEGER
    SET @count = (select count(Id) FROM dbo.CONFIG)
    DECLARE @TimeFrom as DATETIME
    DECLARE @TimeTo as DATETIME
    DECLARE @unit as INTEGER
    WHILE (@count >= 1)
    BEGIN
        SET @TimeFrom = (select TimeStampFrom from CONFIG where Id = @count);
        SET @TimeTo = (select TimeStampTo from CONFIG where Id = @count);
        SET @unit = (select Unit from CONFIG where Id = @count);
        EXECUTE dbo.GetDataSet @Timestampfrom = @timeFrom,@TimestampTo = @timeTo, @Unit = @unit;
        SET @count = @count - 1;
    END
END

The return value is the correct number og tables, but all tables are empty. And when i run the internal procedure on its own, it recives correct values(one table filled with data). The internal procedure in the post looks like this.

ALTER procedure [dbo].[GetDataSet]
@Timestampfrom DATETIME,
@TimestampTo DATETIME,
@Unit INTEGER
AS
select
DATA.Unit,
DATA.CO2Data,
DATA.HumidData,
DATA.TempData,
DATA.TimeStamp
from DATA where Unit = @Unit and TimeStamp between @Timestampfrom and @TimestampTo;

Upvotes: 1

Views: 2977

Answers (1)

EzLo
EzLo

Reputation: 14209

A solution would be creating a temporary table in the outmost procedure and insert the result in the innermost one.

ALTER procedure [dbo].[GetViewData] AS

IF EXISTS( SELECT * FROM dbo.CONFIG )
BEGIN

    IF OBJECT_ID('tempdb..#DataSetResult') IS NOT NULL
        DROP TABLE #DataSetResult

    CREATE TABLE #DataSetResult (
        Unit VARCHAR(100),
        CO2Data VARCHAR(100),
        HumidData VARCHAR(100),
        TempData VARCHAR(100),
        TimeStamp DATETIME)

    DECLARE @count INT = (select count(Id) FROM dbo.CONFIG)

    DECLARE @TimeFrom as DATETIME
    DECLARE @TimeTo as DATETIME
    DECLARE @unit as INTEGER

    WHILE (@count >= 1)
    BEGIN

        SET @TimeFrom = (select TimeStampFrom from CONFIG where Id = @count);
        SET @TimeTo = (select TimeStampTo from CONFIG where Id = @count);
        SET @unit = (select Unit from CONFIG where Id = @count);

        EXECUTE dbo.GetDataSet 
            @Timestampfrom = @timeFrom,
            @TimestampTo = @timeTo, 
            @Unit = @unit;

        SET @count = @count - 1;
    END

    -- Display results
    SELECT * FROM #DataSetResult AS D

END

GO


ALTER procedure [dbo].[GetDataSet]
    @Timestampfrom DATETIME,
    @TimestampTo DATETIME,
    @Unit INTEGER
AS
BEGIN

    INSERT INTO #DataSetResult (
        Unit,
        CO2Data,
        HumidData,
        TempData,
        TimeStamp)
    select
        DATA.Unit,
        DATA.CO2Data,
        DATA.HumidData,
        DATA.TempData,
        DATA.TimeStamp
    from 
        DATA 
    where 
        Unit = @Unit and 
        TimeStamp between @Timestampfrom and @TimestampTo;
END

If you can't change the innermost procedure, you will have to do an INSERT INTO EXEC, which isn't recommended as your can't nest several ones.

ALTER procedure [dbo].[GetViewData] AS

IF EXISTS( SELECT * FROM dbo.CONFIG )
BEGIN

    IF OBJECT_ID('tempdb..#DataSetResult') IS NOT NULL
        DROP TABLE #DataSetResult

    CREATE TABLE #DataSetResult (
        Unit VARCHAR(100),
        CO2Data VARCHAR(100),
        HumidData VARCHAR(100),
        TempData VARCHAR(100),
        TimeStamp DATETIME)

    DECLARE @count INT = (select count(Id) FROM dbo.CONFIG)

    DECLARE @TimeFrom as DATETIME
    DECLARE @TimeTo as DATETIME
    DECLARE @unit as INTEGER

    WHILE (@count >= 1)
    BEGIN

        SET @TimeFrom = (select TimeStampFrom from CONFIG where Id = @count);
        SET @TimeTo = (select TimeStampTo from CONFIG where Id = @count);
        SET @unit = (select Unit from CONFIG where Id = @count);

        INSERT INTO #DataSetResult (
            Unit,
            CO2Data,
            HumidData,
            TempData,
            TimeStamp)
        EXECUTE dbo.GetDataSet 
            @Timestampfrom = @timeFrom,
            @TimestampTo = @timeTo, 
            @Unit = @unit;

        SET @count = @count - 1;
    END

    -- Display results
    SELECT * FROM #DataSetResult AS D

END

Please review the result set data types, as I put VARCHAR for most of them.

Upvotes: 1

Related Questions