Using Case When to Determine whether to update values or select values-CodePudding

Edit: The below question was framed incorrectly by attempting to utilize the CASE WHEN expression in place of an IF statement. Please see the answer provided by schmiel. Hopefully this helps others.

I'm making a tool/report in Report Builder. The main logic is dictated by a parameter that is manually selected with a dropdown called CheckOrUpdate.

What I'm trying to accomplish is if the CheckOrUpdate parameter is set to CHECK I want the report to run a simple Query. If set to UPDATE I want the report to run an update statement.

Here is the Query:

DECLARE @SITE AS NVARCHAR(30)
DECLARE @Password AS NVARCHAR (30)
DECLARE @CheckOrUpdate AS NVARCHAR(30)

--SET @Password = 'Resend'
--SET @SITE = 'SRVCS' 
--SET @CASE = '123456'
--SET @CheckOrUpdate = 'CHECK'

SELECT
CASE 
    WHEN @CheckOrUpdate = 'CHECK' --Just check to verify that records have been updated
        THEN
            (SELECT
            order_num
            ,is_extracted
            ,interface_batch
            ,trans.item_num
            ,item.desc_1
            ,item.desc_2

            FROM trans
            INNER JOIN item on item.item_num=trans.item_num

            WHERE order_num=@CASE AND site_code = @SITE AND is_extracted = 1 AND @Password='Resend')

    WHEN @CheckOrUpdate = 'UPDATE' --Run the update
        THEN
            (UPDATE trans
            SET is_extracted = 0 , interface_batch = NULL
            WHERE order_num=@CASE AND site_code = @SITE AND is_extracted = 1 AND @Password='Resend')
    --ELSE NOTHING
END

I understand that the syntax should go SET > Select > CASE WHEN. Just trying to understand how to go about running a query or running an update.

There is only one dataset in the report I'm making and the dataset is using the query above. The commented out portion is where I was testing the logic in SQL Server.

Any ideas or references someone can point me too? Should I create another dataset and split the two? I couldn't find much in the way of what I'm looking to do.

Background: Application didn't interface info for this record due to interface outage or error and queueing stuff to be resent. Now this is being done manually just creating a tool to speed the process up for end users.

CodePudding user response：

As suggested in the comments by Dan Guzman and droebi you should use a if statement like:

if @CheckOrUpdate = 'CHECK' --Just check to verify that records have been updated
begin
    (SELECT
        order_num
        ,is_extracted
        ,interface_batch
        ,trans.item_num
        ,item.desc_1
        ,item.desc_2
    FROM trans t
    INNER JOIN item  i
    on i.item_num=t.item_num
    WHERE order_num=@CASE 
    AND site_code = @SITE 
    AND is_extracted = 1 
    AND @Password='Resend')
end
else if @CheckOrUpdate = 'UPDATE'
begin
    (UPDATE trans
    SET is_extracted = 0 , interface_batch = NULL
    WHERE order_num=@CASE AND site_code = @SITE AND is_extracted = 1 AND @Password='Resend')
end

Or you can use else instead of else if if you only have two options.

And maybe you don't need the begin and end statement but that's the way I'm writing if statements :)