January 25, 2017 at 12:15 pm
I have a report that I am working on that needs a parameter for vendor. So that the user can select between two different vendors to return data. The data for the two different vendors is in different tables. So what I am trying to do in the query is something like this:
IF @vendor = 'A'
begin
select
.... from tableA
end
else begin
select
.... from tableB
end
However, this is not working. When I preview the report, I get an error about 'The Value expression for the report parameter 'vendor' contains an error: 'Return' statement in a Function, Get, or Operator must return a value.'
I have tried it using a stored procedure as well, but not having any luck. Any ideas?
Thanks
January 25, 2017 at 12:27 pm
This is not the code that the error is referring to. The error specifically mentions a RETURN statement, and there are no RETURN statements in this code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 25, 2017 at 12:32 pm
drew.allen - Wednesday, January 25, 2017 12:27 PMThis is not the code that the error is referring to. The error specifically mentions a RETURN statement, and there are no RETURN statements in this code.Drew
Thanks for trying to help. I figured out the problem. It is not with the query, but with the Parameters. For available values, I put in two literal text values. The problem was that instead of just typing in the values, I put them in as a function and set them like ='VendorX' instead of ="VendorX"
So that was breaking it. I must be tired... Thanks again for replying and trying to help me figure this out.
January 25, 2017 at 12:33 pm
You really don't want a multi statement table valued function. You want to use an inline table valued function. The difference would be huge in performance, over 200 times faster with an iTVF.
Here's an example on how to do it.
CREATE FUNCTION SampleFunction(
@Vendor char(1)
) RETURNS TABLE
AS RETURN
SELECT YourColumns
FROM tableA
WHERE @vendor = 'A'
UNION ALL
SELECT YourColumns
FROM tableB
WHERE @vendor != 'A';
Of course, I might not have the complete picture, and you might be better with a stored procedure, but you gave almost no information.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply