CODEWITHSUNDEEP
sqlsql-serverstored-procedurescommon-table-expression
REW
REW

Reputation: 776

Syntax error using multiple CTEs

I have a rather complicated CTE that I'm attempting to incorporate into a stored procedure. It works when just operating straight from SQL Server Management Studio. When I try to create my stored procedure, I get an error:

Msg 102, Level 15, State 1, Procedure spMyCrazyProc, Line 56

Incorrect syntax near ','.

What have I syntactically done incorrectly when trying to incorporate my CTE into a stored procedure?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spMyCrazyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spMyCrazyProc]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.spMyCrazyProc
    @CompanyId  Int,
    @EmployeeIds varchar(MAX)
AS


;with SelectedEmployees as ( 
    select * from  vwEmployee e
        where e.CompanyId = @CompanyId 
            and (@EmployeeIds is null or @EmployeeIds='' or exists(select ce.SelectedEmployeeId from #myTmpTable ce where ce.SelectedEmployeeId=e.EmployeeId)
), MyStuffA as ( 
    select * from SelectedEmployees
)
select * from MyStuffA

GO

Upvotes: 1

Views: 1173

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280320

Using sensible coding conventions and thinking about readability (indenting, carriage returns) would have yielded this simple error much more clearly. Your code with 500 character-wide lines removed:

;with SelectedEmployees as 
( 
    select * from  vwEmployee e
    where e.CompanyId = @CompanyId 
    and 
    (
      @EmployeeIds is null or @EmployeeIds='' or exists
      (
         select ce.SelectedEmployeeId from #myTmpTable ce 
          where ce.SelectedEmployeeId=e.EmployeeId
      )

----^----- oops! Missing closing paren here.

), MyStuffA as 
( 
  select * from SelectedEmployees
)
select * from MyStuffA

Upvotes: 4

Related Questions