SQL case statement in a View logic

Question

CREATE VIEW [Myview] AS
SELECT 
  CASE
    WHEN 
    DATENAME(dw,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))='Sunday'
    THEN
        SELECT 
            cast(DATEPART(yyyy, getdate()-7) as varchar(4)) as YEAR,
            RIGHT('00'+cast(DATEPART(ww, getdate()-7) as varchar(2)),2) as WEEK;    
    ELSE
        SELECT 
            cast(DATEPART(yyyy, getdate()-14) as varchar(4)) as YEAR,
            RIGHT('00'+cast(DATEPART(ww, getdate()-14) as varchar(2)),2) as WEEK;
GO

Error while creating: Msg 103010, Level 16, State 1, Line 1 Parse error at line: 8, column: 3: Incorrect syntax near 'SELECT'.

Help me out to fix this error. I want to check if the 1st day of the year is Sunday then I would follow the then statement. Then statement suggest to get week id and year.enter image description here

Answer 1

There are multiple problems with your SQL. Firstly you have 3 SELECT statements in your query; 2 of which are inside your CASE Expression. A VIEW is a pseudo table who's definition is defined by a single statement ending in a SELECT. You have 2/3 statements here so it's not a valid definition for a VIEW.

Next, as I emphasised, CASE is an expression in T-SQL, not a Statement. T-SQL does not support Case (Switch) statements, only CASE expressions. CASE expressions return a scalar value; not statements, not boolean results. This means that you can only return one atomic value from the expression, and therefore each expression WHEN and the ELSE must result in a scalar value too.

Looking at what you've attempted here, however, I'm not even sure it belongs as a VIEW. It doesn't reference any tables, which normally a VIEW would. I would, personally, suggest this is better off as a inline table value function, which (if I understand what you are attempting correctly) would look like this:

CREATE FUNCTION dbo.YourFunction()
RETURNS TABLE
AS RETURN
    SELECT CASE V.DayName WHEN 'Sunday' THEN DATEPART(YEAR, DATEADD(DAY, -7,GETDATE())) --I suggest leaving this as a strongly typed int
                          ELSE DATEPART(YEAR, DATEADD(DAY, -7,GETDATE())) --I suggest leaving this as a strongly typed int
           END AS [Year],
           CASE V.DayName WHEN 'Sunday' THEN DATEPART(WEEK, DATEADD(DAY, -7,GETDATE())) --I suggest leaving this as a strongly typed int
                          ELSE DATEPART(WEEK, DATEADD(DAY, -7,GETDATE()))
           END AS [Week]
    FROM (VALUES(DATENAME(WEEKDAY,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))))V(DayName);
GO

I also tidy up some of your SQL> For example I use the same keywords consistently for the date parts like YEARand WEEK (you used both yy and yyyy for year), and replace the logic that treats dates like an int with explicit DATEADD logic, as that works with any date and time data type (not just the old (small)datetime data types).

You would then call the function like so:

--On it's own
SELECT [Year], [Week]
FROM dbo.YourFunction();

--When using other tables
SELECT {Some Columns}
FROM dbo.YourTable YT
     CROSS APPLY dbo.YourFunction() YF;