Using with in SQL Server Function

Question

Post reply

Using with in SQL Server Function

az1862

SSC Veteran

Points: 262
More actions
October 29, 2013 at 5:29 pm

#300597

Why this doesn't work?
I have to use code 2 as my atual scenerio is more complex as it brings data from multiple queries and should filter it in with clause before joining it.
So
with
tab1 AS (Select * from a where a.c1 < 20),
tab2 AS (Select * from a where a.c2 > 2000),
tab3 AS (Select * from a where a.c3 < 45),
tab4 AS (Select * from a where a.c4 < 175)
then select like
Select * from tab1
INNER JOIN tab2 on tab1.c1 = tab2.c1
INNER JOIN tab3 on tab1.c1 = tab3.c1
INNER JOIN tab4 on tab1.c1 = tab4.c1
-------- Code 1; Does Work--------------------
Create FUNCTION Test22 ()
RETURNS
@tmp1 TABLE
(
[Year] INT
, [Month] VARCHAR(255)
, [Customer] VARCHAR(255)
)
AS
BEGIN
DECLARE @Begin_Date DATE , @END_Date DATE
SET @Begin_Date = getdate() -7
SET @End_Date = getdate()
'commenting to keep it simple
'SELECT @Begin_Date = [Params1_Date] ,@END_Date = [Params2_Date] FROM [tbl_Parameters]
' WHERE Params_Name = 'Status_Rpt'
INSERT @tmp1
SELECT [Year]
,[Month]
,[Customer]
FROM [temp_table]
RETURN
END
---------------------------------
--------Code 2 ; Doesn't Work--------------------
Create FUNCTION Test22 ()
RETURNS
@tmp1 TABLE
(
[Year] INT
, [Month] VARCHAR(255)
, [Customer] VARCHAR(255)
)
AS
BEGIN
DECLARE @Begin_Date DATE , @END_Date DATE
SET @Begin_Date = getdate() -7
SET @End_Date = getdate()
'commenting to keep it simple
'SELECT @Begin_Date = [Params1_Date] ,@END_Date = [Params2_Date] FROM [tbl_Parameters]
' WHERE Params_Name = 'Status_Rpt'
INSERT @tmp1
With aa AS (Select * from [temp_table] where c_date between @Begin_Date AND @End_Date)
SELECT [Year]
,[Month]
,[Customer]
FROM AA
RETURN
END
---------------------------------
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

You have your syntax wrong, try this:

;With aa AS (Select * from [temp_table] where c_date between @Begin_Date AND @End_Date)

INSERT @tmp1

SELECT [Year]

,[Month]

,[Customer]

FROM AA

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);