CODEWITHSUNDEEP
sql-server-2008sp-executesql
rvphx
rvphx

Reputation: 2402

EXECUTESQL error

Here is the following piece of code that I am trying to execute on SQL Server. 

DECLARE @string NVARCHAR(MAX) = '
    CREATE PROC [dbo].[Trend]
    @slsID NVARCHAR(20)
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @BeginningRange varchar(20),
    @EndingRange varchar(20)
    SET @EndingRange = ''12*13''
    SET @BeginningRange = ''12*02''
    ;WITH CTE1 AS(
             SELECT 
             dbo.Field1,dbo.Field2,dbo.Field3
             FROM dbo.Table1 join dbo.Table2 where...conditions
             weekNum BETWEEN (@BeginningRange) AND (@EndingRange)
                 )
    SELECT * FROM CTE1 
    UNPIVOT
      ( numbers for type in (Field1, Field2, Field3, Field4)
      ) as p PIVOT
      (
      Sum(numbers) for
      WeekNum in ([12*02],[12*03],[12*04],[12*05],[12*06],[12*07],[12*08],[12*09],[12*10],                                                         [12*11],[12*12],[12*13])
      ) as q
      END
'
    EXECUTE SP_EXECUTESQL @STRING

When I try to run this, it errors out saying that

"Incorrect syntax near the keywor 'as'"

I took this code out and executed it separately and it didn't error out. Am I missing something here?

Upvotes: 0

Views: 127

Answers (3)

HLGEM
HLGEM

Reputation: 96552

where...conditions

This won't pass a syntax check. If you have removed the actual conditions it may be that this is where your error is. And:

dbo.Table1 join dbo.Table2

has no ON clause

I saw both of these by doing a syntax check on the results of print @string which is the first step you should have taken to find the issue. I still say that based on what you gave us there is no reason at all to use dynamic SQl and it is a poor practice to use dynamic SQL if you don't need it.

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17957

PIVOT and UNPIVOT clauses each require two closing parentheses.

UNPIVOT (... FOR ... IN (...) ) AS ...

PIVOT (... FOR ... IN (...) ) AS ...

Upvotes: 0

Ray
Ray

Reputation: 21905

Look like missing parentheses around the parameter to the procedure.

One trick you can use is the print out the sql statement and then try to run that - the error message might give you more info

print @STRING

Upvotes: 1

Related Questions