common table expression in a stored procedure

• solus

  Hall of Fame

  Points: 3495

  More actions

  May 15, 2017 at 3:13 am

  #325596

  im trying to create a common table expression inside a stored procedure, but i getting an error

  Incorrect syntax near the keyword 'AS'.

  here the sql

  
  WITH cte AS (
          SELECT ORG_FULLNAME,[STATUS], END_USER_ORG
          FROM (
              SELECT END_USER_ORG,
                      ORG_FULLNAME,
                      [STATUS],
                      row_number() OVER (partition by lower(END_USER_ORG) order by END_USER_ORG) AS rn
              FROM TmpContract
      ) tc
  WHERE rn = 1)
  INSERT INTO dbo.Organisation(OrganisationName,[STATUS], END_USER_ORG)
  SELECT ORG_FULLNAME,[STATUS], END_USER_ORG FROM cte
  WHERE cte.END_USER_ORG NOT IN
  (SELECT END_USER_ORG FROM dbo.Organisation)

  its works fine outside the stored procedure just not inside it

• Thom A

  SSC Guru

  Points: 99130

  More actions

  May 15, 2017 at 3:22 am

  #1942489

  What's your full SQL statement, with your CREATE statement? I imagine that your CREATE statement is wrong, not your CTE statement. It should look something like this:
  CREATE PROC Sample_SP /*parameter declarations here*/ AS
  WITH cte AS (
  SELECT ORG_FULLNAME,[STATUS], END_USER_ORG
  FROM (
  SELECT END_USER_ORG,
  ORG_FULLNAME,
  [STATUS],
  row_number() OVER (partition by lower(END_USER_ORG) order by END_USER_ORG) AS rn
  FROM TmpContract
  ) tc
  WHERE rn = 1)
  INSERT INTO dbo.Organisation(OrganisationName,[STATUS], END_USER_ORG)
  SELECT ORG_FULLNAME,[STATUS], END_USER_ORG FROM cte
  WHERE cte.END_USER_ORG NOT IN
  (SELECT END_USER_ORG FROM dbo.Organisation)
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk