CODEWITHSUNDEEP
sql-servert-sqlcasestored-functions
litpost
litpost

Reputation: 105

User defined function with CASE statement

I am very new to SQL Server. Here I try to create UDF with CASE statement to get order status from database STRDAT depending on various Order dates entered. Here is the code:

USE STRDAT
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'dbo.GetOrderStatus', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetOrderStatus ;
GO

CREATE FUNCTION dbo.GetOrderStatus(@lngOrderID int)
RETURNS varchar(50)
AS
BEGIN
    WITH MyData AS  
        (
        SELECT 
            ReservedDate AS Res,
            ConfirmedDate AS Conf,
            ProcessedDate AS Procs,
            ProducedDate AS Prod,
            ShippedDate AS Ship,
            RefusingReason AS Refs,
            CancelledDate AS Canc
        FROM tbl_Order 
        WHERE OrderID = @lngOrderID
        )

        SELECT GetOrderStatus= CASE
            WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL AND Ship IS NULL AND Canc IS NULL AND Refs is null
            THEN 'Naujas'

            WHEN NOT Canc IS NULL
            THEN 'Atšauktas'

            WHEN NOT Refs IS NULL
            THEN 'Atmestas'

            WHEN NOT Ship IS NULL
            THEN 'Atkrautas'

            WHEN NOT prod IS NULL
            THEN 'Pagamintas'

            WHEN NOT Procs IS NULL
            THEN 'Apdirbtas'        

            WHEN NOT Conf IS NULL
            THEN 'Patvirtintas'

            ELSE 'N/A'
            END
        FROM MyData     
    END

It is one of my first functions and I cannot clarify why I get error on line CREATE FUNTION ...:

Select statements included within a function cannot return data to a client.

Upvotes: 1

Views: 10967

Answers (2)

Bohemian
Bohemian

Reputation: 425098

  1. Add the RETURN keyword
  2. Wrap the query in brackets
  3. Remove GetOrderStatus=

Try this:

RETURN (WITH MyData AS
      ...
      SELECT CASE
      ...
      FROM mydata);

BTW this:

WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL AND Ship IS NULL AND Canc IS NULL AND Refs is null

can be more elegantly expressed as:

WHEN COALESCE(res, conf, PROCS, PROD, Ship, Canc, Refs) IS NULL

Upvotes: 1

Mukund
Mukund

Reputation: 1689

you are missing return statement in the end of function.

and do this 

declare @GetOrderStatus varchar(50)

set @GetOrderStatus  = (select CASE
            WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL 
            AND Ship IS NULL AND Canc IS NULL AND Refs is null
            THEN 'Naujas'

             WHEN NOT Canc IS NULL
             THEN 'Atšauktas'

             WHEN NOT Refs IS NULL
             THEN 'Atmestas'

             WHEN NOT Ship IS NULL
             THEN 'Atkrautas'

             WHEN NOT prod IS NULL
             THEN 'Pagamintas'

             WHEN NOT Procs IS NULL
             THEN 'Apdirbtas'        

             WHEN NOT Conf IS NULL
             THEN 'Patvirtintas'

        ELSE 'N/A'
        END
    FROM MyData 
   return @GetOrderStatus

Upvotes: 4

Related Questions