CODEWITHSUNDEEP
sql-serverstored-procedures
GH Foo
GH Foo

Reputation: 5

Executing stored procedure returns syntax error but running query works

I have a stored procedure that returns a syntax error when executed. However running the query on SQL Server Management studio works.

The syntax error is:

Msg 102, Level 15, State 1, Line 96
Incorrect syntax near 'se'.

The full stored procedure is here: https://pastebin.com/nnQ65KPM

I have narrowed the problem to the last CTE. When this block is removed the stored procedure executes fine. All CTEs used in WellTestDetails_CTE work fine as well.

WellTestDetails_CTE (testmonth, well, result) as
(
    select 
        b.month as testmonth, a.well, a.result 
    from 
        (select 
             well, result, result_no
         from 
             (select * from WellTest1 
              union 
              select * from WellTest2) a 
         join     
             [Digital_Ecosystem_DEV].[dbo].[OrgAssigments] b on a.SUBFACILITY = b.SubFacility 
         where  
             b.team = ''X'') x 
    join 
        WellTestGrouped_CTE y on x.result_no = y.result_no       
)
select * 
from WellTestDetails

Upvotes: 0

Views: 424

Answers (1)

osiris_v6
osiris_v6

Reputation: 462

I think @SQL variable in DECLARE @SQL nvarchar(4000) is too small for all the dynamic SQL, try DECLARE @SQL nvarchar(MAX).

Upvotes: 3

Related Questions