CODEWITHSUNDEEP
sqlstored-procedurescase
Jay
Jay

Reputation: 258

Getting the result of a Case statement from SQL back to the application

I have a form that users can use to enter a serial number to see if a part is still under warranty or not.

warranty search

When they click the search button it connects to the database and runs my stored procedure that searches for the serial number they entered and checks a DATETIME column called OFF_WARRANTY which is the date until which the part is still under warranty. Whether or not that date is before or after @today determines if the part is still under warranty.

Here's my stored procedure:

ALTER PROCEDURE [dbo].[WarrantyLookup] 
    -- Add the parameters for the stored procedure here
    @serialNumber VARCHAR(150),
    @warrantyStatus VARCHAR(150) OUTPUT

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @today DATE
    SET @today = GETDATE()

    IF(SELECT COUNT(1) FROM <serial number table> WHERE SERIAL_NUMBER = @serialNumber) = 1
        SELECT OFF_WARRANTY,
            CASE
                WHEN OFF_WARRANTY >= @today THEN 'In warranty'
                WHEN OFF_WARRANTY < @today THEN 'Out of warranty'
                WHEN OFF_WARRANTY IS NULL THEN 'none'
                ELSE 'multiple'
            END
        FROM <serial number table>
        WHERE SERIAL_NUMBER = @serialNumber
END

A serial number can be associated with multiple items in our system so the first thing I do is use Count() to make sure there's only 1. Then I'm trying to determine the items' warranty status and I made an output variable for that called @warrantyStatus.

The thing I'm not sure about is how exactly to set @warrantyStatus to the result of my CASE statement. In my mind I just want to do something fairly straightforward like:

SET @warrantyStatus = <the result of my CASE statement>

But I don't know how to type that, exactly? I showed a senior developer in my department and he said something about using a SqlDataReader and then doing if(rdr.HasRows) but didn't really clarify how. Can I use a SqlDataReader to read the results of my CASE statement?

I appreciate any help anyone can offer. I feel like I'm almost certainly over-complicating it, but I'm just not connecting the last dots to make this work.

Thanks,

Jay

Upvotes: 0

Views: 82

Answers (2)

Ross Bush
Ross Bush

Reputation: 15175

You had it almost exactly right. Except you can't mix a query that returns data when setting variables. If you needed the other fields then I guess the most performant way would be to SELECT the results into a @TempTable.

SELECT 
   @warrantyStatus = 
       CASE
            WHEN OFF_WARRANTY >= @today THEN 'In warranty'
            WHEN OFF_WARRANTY < @today THEN 'Out of warranty'
            WHEN OFF_WARRANTY IS NULL THEN 'none'
            ELSE 'multiple'
       END

Conversely, the SET operation would also work here.

 SET @warrantyStatus=
    (
       SELECT
            CASE
                WHEN OFF_WARRANTY >= @today THEN 'In warranty'
                WHEN OFF_WARRANTY < @today THEN 'Out of warranty'
                WHEN OFF_WARRANTY IS NULL THEN 'none'
                ELSE 'multiple'
            END
        FROM <serial number table>
        WHERE SERIAL_NUMBER = @serialNumber
    )

Here you can insert into a @Table to return the other rows.

DECLARE @Result TABLE
(
    OFF_WARRANTY NVARCHAR(50),
    CalcWarranty NVARCHAR(50)
)


IF(SELECT COUNT(1) FROM <serial number table> WHERE SERIAL_NUMBER = @serialNumber) = 1
INSERT @Result
SELECT 
    OFF_WARRANTY,
    CalcWarranty = 
    CASE
        WHEN OFF_WARRANTY >= @today THEN 'In warranty'
        WHEN OFF_WARRANTY < @today THEN 'Out of warranty'
        WHEN OFF_WARRANTY IS NULL THEN 'none'
        ELSE 'multiple'
    END
FROM <serial number table>
WHERE SERIAL_NUMBER = @serialNumber

SELECT @warrantyStatus = CalcWarranty FROM @Result

SELECT
    OFF_WARRANTY
FROM
    @Result

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270061

Why not just create a view and use a select?

CREATE VIEW v_warranty_stuff as
    SELECT SERIAL_NUMBER, OFF_WARRANTY,
           (CASE WHEN OFF_WARRANTY >= CAST(GETDATE() as DATE) THEN 'In warranty'
                 WHEN OFF_WARRANTY < CAST(GETDATE() as DATE) THEN 'Out of warranty'
                 WHEN OFF_WARRANTY IS NULL THEN 'none'
                 ELSE 'multiple'
            END) as warrantystatus
    FROM <serial number table>;

Then you can simply call:

select warranty_status
from v_warranty_stuff
where SERIAL_NUMBER = @serialNumber;

Upvotes: 0

Related Questions