Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using with in SQL Server Function Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 5:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:25 PM
Points: 4, Visits: 10
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

---------------------------------
Post #1509615
Posted Tuesday, October 29, 2013 6:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 1,651, Visits: 5,192
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1509624
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse