0

I have a standard select query which returns a set of results :-

SELECT
    c.CASEID,
    c.Event,
    c.ACTION,
    c.CYCLE,
    c.PROPERTYTYPE,
    c.COUNTRYCODE,
    c.STATUSCODE,
    c.CRITERIA,
    c.DATE,
    c.TITLE,
    c.DESCRIPTION
FROM CASES c

WHERE c.DATE = GETDATE() 
and  c.EVENT in (1, 2, 3, 4)

I'm trying to use some of the fields from each row of the query as parameters for a stored procedure which returns a single row of 4 columns :-

Amount1, Amount 2, Cost, Currency.

The stored procedure would run as :-

exec AmountsCalc c.CASEID, 105, 96, c.ACTION, c.EVENT, c.CYCLE

This would return :-

Amount1 Amount2 Amount3 Cost Currency
10.00 20.00 30.00 40.00 GBP

I'd like to combine the stored procedure result with each row from the original query where the c.CASEID is the link between each row of the results and SP, to use in an SSRS report.

I have spent a lot of time looking for an answer and have come up with a solution, but it doesn't seem like an efficient solution and I was hoping there might be a better one but I'm at the limits of my experience.

My solution is to have a dataset in SSRS with the original query, pass the caseid to a parameter and then have another dataset query that runs the stored procedure for each caseid in the parameter. Then use a lookup expression between the first dataset and the stored procedure dataset.

As the query for the stored procedure is using almost an identical select query as the original, it seems like there might be a better way than running the query twice. The query I’m using to retrieve the data from the stored procedure is :-

DECLARE @CASEFEESCURSOR CURSOR,
        @CASEID INT,
        @Event int,
        @Cycle smallint,
        @Action nvarchar(10)

DECLARE     @SPResults TABLE (
            AMOUNT1             decimal (11,2)  NULL, 
            AMOUNT2             decimal (11,2)  NULL,
            COST                decimal (11,2)  NULL,
            CURRENCY            nvarchar(3)     NUll
)


Create  table #TEMPCASEFEES (
            CASEID              int,
            AMOUNT1             decimal (11,2)  NULL, 
            AMOUNT2             decimal (11,2)  NULL,
            COST                decimal (11,2)  NULL,
            CURRENCY            nvarchar(3)     NUll
            )

SET @CASEFEESCURSOR = CURSOR FOR
--main query with minimal select fields to match main query in report 
            SELECT
                c.CASEID,
                c.Event,
                c.ACTION,
                c.CYCLE,
                c.CRITERIA,
        FROM CASES c
        WHERE c.DATE = GETDATE() 
        and  c.EVENT in (1, 2, 3, 4)
        and c.CASEID in @pCASEID --parameter from first dataset in ssrs report 
    ORDER BY c.CASEID;

OPEN @CASEFEESCURSOR;  

--- load first row           
FETCH NEXT FROM @CASEFEESCURSOR 
INTO  @CASEID, @Event, @Action, @Cycle, @CRITERIA

WHILE @@FETCH_STATUS = 0  
BEGIN  
        --- execute proc 
        INSERT INTO @SPResults
        EXEC AmountsCalc @CASEID, 105, 96, @Action, @Event, @Cycle

        INSERT INTO #TEMPCASEFEES (CASEID, Amount1, Amount2, Cost, Currency)
        SELECT @CASEID, Amount1, Amount2, Cost, Currency FROM @SPResults

        DELETE FROM @SPResults      
        
        --- get next row           
        FETCH NEXT FROM @CASEFEESCURSOR
        INTO @CASEID, @Event, @Action, @Cycle, @CRITERIA
END;  
CLOSE @CASEFEESCURSOR;  
DEALLOCATE @CASEFEESCURSOR;

select * from #TEMPCASEFEES order by CASEID 

Any suggestions would be appreciated.

I have looked at turning the called stored procedure into a function but I don't think I can as it calls another stored procedure. I also can't edit the stored procedure.

I do have a working solution but would like to know if there is a better way to do this.

5
  • What does AmountsCalc do, and why can it not be a Table Valued Function instead? Can you show its code Feb 10 at 18:56
  • It’s a vendor specific stored procedure and calls other stored procedures and my (limited) understanding was that a function can’t call a stored procedure.
    – UK1539
    Feb 10 at 20:21
  • Correct it can't, but ideally it should be transformed into a function, so if you show that code we can show you how to do that. In other words: the efficient solution to using a cursor is not to use it as it's really slow. Feb 11 at 1:51
  • Thank you. I’ll see if I can post it tomorrow.
    – UK1539
    Feb 11 at 10:14
  • Just to clarify, which part of this process are you unhappy with? If there's a performance problem, see How To Get Answers To SQL Server Performance Questions. If you're just looking for alternate approaches, this is about as good as it's going to get for the requirements. Feb 11 at 16:27

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.