How to populate a dataset in SSRS from a stored proc

Question

I'm creating a SSRS report from a stored procedure in SQL, when i add a dataset to the report i'm finding i'm having to add this to the top of my procedure in order for fields to be returned into my data set from the stored proc:

IF 1=2 SELECT '' as [Date], '' as [Portfolio ID], '' as [Portfolio Base Ccy], 0 as [All in Market Value Base], 0 as [All in Market Value Converted]

Why would I have to do this and why does SSRS not simply pick up:

SELECT [Date], [Portfolio ID],   [Portfolio Base Ccy], [All in Market Value Base], [All in Market Value Converted]
FROM @Worktable

from the bottom of my procedure or

SELECT * FROM @Worktable

?

EDIT: Ok i think i've narrowed down my issue in my procedure to the face that i have a debug parameter in my procedure. So if this parameter is set to 'Y' then i show each step of my procedure, which makes it easier for debugging (i can add an example if need be). Also at the end of my procedure i have an if statement for my final result set which says:

IF @ReportType IN ('SSD', 'TTD', 'STD')
BEGIN
SELECT.........
END

IF @ReportType IN ('SS', 'TT', 'ST')
BEGIN
SELECT.........
END

Could this be an issue as well?

Are you doing something special with the stored procedure? Within the dataset dialog, what happens when you simply select the source procedure? Also, can you please post the name of your procedure so we can see what you are doing? — billinkc
Normally you'd set the "Command Type" property of the dataset to "Stored Procedure", and for the query of the dataset just enter the name of the stored procedure. Can you perhaps add to your question how you've set up the properties of the dataset? — Jeroen

chrissy p chrissy p · Accepted Answer · 2012-10-05T10:10:02

I worked out my issue, all the way through my procedure i have a debug running for trouble shotting as below:

IF @Debug = 'Y'
    BEGIN
        SELECT @Section AS Section
        SELECT * FROM #Worktable ORDER BY [Portfolio ID]
    END

This caused ssrs to get confused as to what is the metadata for the report. I have now fixed my procedure to cause this not to occur and now i don't need the "IF 1=2 SELECT........"

How to populate a dataset in SSRS from a stored proc

1 Answers