If statement in Dataset query

  • 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

  • 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

  • drew.allen - Wednesday, January 25, 2017 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

    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.

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply