After trying a lot on Google, I ran out of luck and here I am seeking your help. I am trying to create a dataset in SSRS using a Stored Procedure and I am having hard time. Could someone please shed me light of what I am missing?
Here is the how my Stored Procedure looks like:
CREATE PROCEDURE [dbo].[usp_GetUserData](@ReportRunDate DATE = NULL,
@ReportName VARCHAR(50) = NULL)
AS
BEGIN
WITH CTE
AS (SELECT [USER_ID], [Name], [Address], [DOB], [Status], [UserType], [DateAdded]
FROM [dbo].[User_Information]
WHERE [DateAdded] = @ReportRunDate)
SELECT *
INTO #UserReport
FROM CTE;
IF(@ReportName = 'Internal')
BEGIN
SELECT [USER_ID], [Name], [Address], [DOB]
FROM #InternalReport
WHERE [UserType] = 'Internal';
SELECT *
FROM #InternalReport;
END;
IF(@ReportName = 'External')
BEGIN
SELECT [Name],[Status]
FROM #ExternalReport
WHERE [UserType] = 'External';
SELECT *
FROM #ExternalReport;
END;
END;
I've changed the order of parameters as mentioned by many people in SO but that didn't help either as well.