Executing multiple WITH clauses inside a T-SQL function

Question

How do I include multiple WITH clauses inside of a T-SQL function?

So I have these WITH clauses:

WITH a AS (
    SELECT 
        *
    FROM 
        dbo.mydb
    WHERE 
        condition
),
b AS (
    SELECT 
        * 
    FROM (
        SELECT 
            big switch case   
        FROM 
            dbo.myotherdb
        LEFT JOIN 
            dbo.anotherone
        WHERE 
            condition
),
c AS (
    SELECT 
        a.*
    FROM 
        a
    LEFT JOIN 
        b
)

Now, I would like to use my c clause inside of a function as such:

CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
    DECLARE @MyVariable DECIMAL
    SET @MyVariable = (
                        CASE
                        WHEN ( 
                            SELECT TOP(1) c.something 
                            FROM c AS c 
                            ) IS NOT NULL THEN 1
                        WHEN (
                            SELECT TOP(1) c.something
                            FROM c AS c 
                            WHERE c.something = 2
                            ) IS NOT NULL THEN 2 - dbo.otherfunction()
                        END)
RETURN @MyVariable
END GO

Where do I have to put my WITH clauses? Wherever I put them, I get a syntax error. Before my function declaration, inside after de the begin, before the select in the parantheses.

Edit: I modified my example function to look more like the real problem I have. Instead of a select statement, I need to do a switch case.

Okay so this is what the solution was:

CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
    DECLARE @MyVariable DECIMAL
WITH a AS (
    SELECT 
        *
    FROM 
        dbo.mydb
    WHERE 
        condition
),
b AS (
    SELECT 
        * 
    FROM (
        SELECT 
            big switch case   
        FROM 
            dbo.myotherdb
        LEFT JOIN 
            dbo.anotherone
        WHERE 
            condition
),
c AS (
    SELECT 
        a.*
    FROM 
        a
    LEFT JOIN 
        b
)
    SELECT @MyVariable = (
                        CASE
                        WHEN ( 
                            SELECT TOP(1) c.something 
                            FROM c AS c 
                            ) IS NOT NULL THEN 1
                        WHEN (
                            SELECT TOP(1) c.something
                            FROM c AS c 
                            WHERE c.something = 2
                            ) IS NOT NULL THEN 2 - dbo.otherfunction()
                        END)
RETURN @MyVariable
END GO

Answer 1

You put them inside the function, as that's where the statement go. Presumbly (you don't show your attempt(s)) you tried WITH CTE AS (...) SET @Variable... however a CTE cannot be followed by a SET, it must be a SELECT or DML statement. You also have a syntax error in your CTE, as you don't close the parenthesis for your derived table, nor name it.

Fixing these it would look like this:

CREATE FUNCTION dbo.MyFunction ()
RETURNS decimal --(p,s) required
AS BEGIN
    DECLARE @MyVariable decimal; --(p,s) required
    WITH
    a AS
        (SELECT *
         FROM dbo.mydb
         WHERE condition = 1),
    b AS
        (SELECT *
         FROM (SELECT bigswitch AS cased
               FROM dbo.myotherdb
                    LEFT JOIN dbo.anotherone ON 1 = 1
               WHERE condition = 1) a 
    ),
    c AS
        (SELECT a.*
         FROM a
              LEFT JOIN b ON 1 = 1)
    SELECT @MyVariable = c.firstdecimal
    FROM c;
    RETURN @MyVariable;
END;
GO

Though this is unlikely a good idea, use an inline Table Value Function instead:

CREATE FUNCTION dbo.MyFunction ()
RETURNS table
AS
    RETURN WITH
           a AS
               (SELECT *
                FROM dbo.mydb
                WHERE condition = 1),
           b AS
               (SELECT *
                FROM (SELECT big AS switchcase
                      FROM dbo.myotherdb
                           LEFT JOIN dbo.anotherone ON 1 = 1
                      WHERE condition = 1) a --Alias goes here
           ),
           c AS
               (SELECT a.*
                FROM a
                     LEFT JOIN b ON 1 = 1)
    SELECT c.firstdecimal
    FROM c;
GO