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

  ---------------------------------

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  October 29, 2013 at 6:48 pm

  #1662587

  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);

• 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]