x

Syntax error when creating a stored procedure with multiple PIVOTs

Hello, I am new to SQL and very new to SQL 2005. I am trying to create a stored procedure for the first time using PIVOT. When I run the query, it looks great but as soon as I try to CREATE PROCEDURE, I get the error: Msg 102, Level 15, State 1, Procedure SP_IRProcedures, Line 90 Incorrect syntax near '(', which brings me to the open parentheses right next to the word PIVOT. I am not sure what to do to remedy the situtation. I have not included the entire query but can if necessary.

SELECT Site, [Name], Mnemonic, DictatedBy, Vascular, FiscalYear = @fy,
       SUM(ISNULL(CASE WHEN ServiceDate BETWEEN @fyb and @fye THEN ISNULL(PatientCount,0) END,0)) AS 'FY'
INTO #TempIRVascular3
FROM #TempIRVascular2
GROUP BY Site,  Vascular, Mnemonic, [Name], DictatedBy
ORDER BY Site,  Vascular DESC, Mnemonic, [Name], DictatedBy

SELECT *
INTO #TempIRVascular31
FROM #TempIRVascular3
PIVOT(
    	SUM(FY)
        FOR DictatedBy
        IN ([BRADE],[OSTAR],[MADVI],
            [BULCH],[MENBE],[DENED])
      ) AS P
GROUP BY Site, FiscalYear, Vascular, Mnemonic, [Name], [BRADE],[OSTAR],[MADVI],
            [BULCH],[MENBE],[DENED]
ORDER BY Site, FiscalYear DESC, Vascular DESC, Mnemonic, [Name]
DROP table #TempIRVascular3

Thank you, Kelly

more ▼

asked Feb 19 '10 at 04:02 PM in Default

Kelly Batch gravatar image

Kelly Batch
11 1 1 1

Yes, it is at 80. It is supposed to be at 90, right...? I just read that. How do I make the change?

Thank you so much for responding so quickly!
Feb 19 '10 at 04:34 PM Kelly Batch
Sorry, I didn't see this comment because you added it to your question rather than the answer! I've edited the answer below to include details on how to change it. And yes, 90 is the default compatibility level for 2005.
Feb 20 '10 at 09:38 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Is your database running under compatibility level 80, by any chance? Check this with sp_dbcmptlevel...

If you want to change the compatibility level, then you can use ALTER DATABASE Compatibility Level. Be sure to read through this page carefully, because changing the compatibility level up does have implications.

more ▼

answered Feb 19 '10 at 04:20 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x401
x76

asked: Feb 19 '10 at 04:02 PM

Seen: 1202 times

Last Updated: Feb 19 '10 at 04:17 PM