How do I include multiple WITH clauses inside of a T-SQL function?
So I have these WITH clauses:
<code>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
)
</code>
<code>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
)
</code>
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:
<code>CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
DECLARE @MyVariable DECIMAL
SET @MyVariable = (
SELECT
c.firstdecimal
FROM
c as c
)
RETURN @MyVariable
END GO
</code>
<code>CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
DECLARE @MyVariable DECIMAL
SET @MyVariable = (
SELECT
c.firstdecimal
FROM
c as c
)
RETURN @MyVariable
END GO
</code>
CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
DECLARE @MyVariable DECIMAL
SET @MyVariable = (
SELECT
c.firstdecimal
FROM
c as c
)
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.