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, 2010 at 04:02 PM in Default

avatar 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, 2010 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, 2010 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, 2010 at 04:20 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Feb 19, 2010 at 04:02 PM

Seen: 1516 times

Last Updated: Feb 19, 2010 at 04:17 PM

Copyright 2018 Redgate Software. Privacy Policy