Cannot save query as a View in sql 2008

Hi Experts,
I have a piece of code I would like to save as a VIEW but when I try to save the VIEW, I receive an error gui
"Incorrect syntax near the key word 'declare'"
A second gui then appears

User canceled out of save dialog
 (MS Visual Database Tools)

Can I save a VIEW with a declare statement?

The code I used is :

declare @dt1 as datetime, @dt2 as datetime
set @dt1 = '01-' + right('00' + cast(month(DATEADD(m,-6,getdate())) as varchar(2)),2) + '-' + cast(YEAR(DATEADD(m,-6,getdate())) as varchar(4))
set @dt2 = '01-' + right('00' + cast(month(getdate()) as varchar(2)),2) + '-' + cast(YEAR(getdate()) as varchar(4))
SELECT     TOP (100) PERCENT [FC] AS S ,month ([FD]) as D, SUM([FP]) AS F, SUM([CFPC]) AS C, SUM([TOB]) AS T
FROM         dbo.OPS
WHERE     ([CFPC] > 0) AND  CONVERT(VARCHAR(10),([FD]), 101)  >= CONVERT(VARCHAR(10),(@dt1) , 103)

GROUP BY [FC], month ([FD])
ORDER BY S,D
chrislindsayAsked:
Who is Participating?
 
tigin44Commented:
use it like this
SELECT     TOP (100) PERCENT [FC] AS S ,month ([FD]) as D, SUM([FP]) AS F, SUM([CFPC]) AS C, SUM([TOB]) AS T
FROM         dbo.OPS
WHERE     ([CFPC] > 0) AND  CONVERT(VARCHAR(10),([FD]), 101)  >= CONVERT(VARCHAR(10),('01-' + right('00' + cast(month(DATEADD(m,-6,getdate())) as varchar(2)),2) + '-' + cast(YEAR(DATEADD(m,-6,getdate())) as varchar(4))) , 103)

GROUP BY [FC], month ([FD])
ORDER BY S,D

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
so as tigin44 has shown in his example
the answer to your question of "Can I save a VIEW with a declare statement?" is NO, sorry.  You will have to replace any references to your @dt1 and @dt2 with the expressions you have in your set statements.
0
 
lofCommented:
if you need to use variables and more complicated features you should use table-valued function rather than view. Small change in naming and CREATE syntax but you query it almost exactly the same.

http://technet.microsoft.com/en-us/library/ms191165.aspx

but SETs do not look/perform good so it may be worth changing it to something what tigin44 suggested this time (and whenever possible.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Anthony PerkinsCommented:
Also, doing the following kludge:

SELECT TOP 100 PERCENT
...
ORDER BY S, D

May have worked in MS Access and earlier versions of MS SQL Server, but (thankfully) is totally pointless in MS SQL Server 2005/2008
0
 
chrislindsayAuthor Commented:
Thanks for your help, after the change the View works well.  Thanks to all the other comments which were useful
0
 
chrislindsayAuthor Commented:
One note of comment administrators.  Accepted solution can not be seen in experts-exchange/mobile on iphone as maybe because of format issues of code snippet box.

Thanks for all the other comments Experts, much appresiated!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.