July 28, 2017 at 2:40 pm
I'm trying to change this sql query from if-else to set-based. However, the first set-based query is picked up by SSRS even if it has 0 records.
Original:
IF @OutputType='WEB'
BEGIN
SELECT ProductID,
ProductName
FROM Product
END
ELSE IF @OutputType = 'CSV'
BEGIN
SELECT ProductID,
ProductName,
ProductDate
FROM Product
END
set-based
SELECT ProductID,
ProductName
FROM Product
WHERE @OutputType='WEB'
SELECT ProductID,
ProductName,
ProductDate
FROM Product
WHERE @OutputType='CSV'
So if the parameter passed is 'CSV' in the set-based query, it returns 0 rows and more than 0 rows in the second query. However, SSRS the dataset would return 0 rows because it'll pick up the result set from the first query.
Is there a way to solve this problem? Or do I have to rely on the if-else logic.
July 28, 2017 at 5:12 pm
You've got two result-sets there - SSRS will only read the first result-set and ignore the second. UNION ALL them to get the expected result, like below. Note the NULL ProductDate, which makes both result-sets have the same number of columns, required by the UNION operator.
SELECT ProductID,
ProductName,
NULL AS ProductDate
FROM Product
WHERE @OutputType='WEB'
UNION ALL
SELECT ProductID,
ProductName,
ProductDate
FROM Product
WHERE @OutputType='CSV'
August 1, 2017 at 2:31 pm
That was easy. Thanks Andrew!
August 1, 2017 at 4:55 pm
Although late to the party, why parse the table twice?
[Code]SELECT ProductID, ProductName,
CASE @OutputType WHEN 'CSV' THEN ProductDate END AS ProductDate
FROM Product;[/code]
(Typed on phone, apologies if any code typing errors).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply