Using variables in Views

Jimbocephus · March 10th, 2008, 11:04 AM

Is it possible to declare variables in a View? I am trying to create the following view:

CREATE VIEW EMpsView_vw
AS

DECLARE @count Int
DECLARE @mon varchar(3)
SELECT @count = 12
WHILE @count <> 0
BEGIN
    SELECT @mon =
    CASE @count
        WHEN 1 THEN 'Jan'
        WHEN 2 THEN 'Feb'
        WHEN 3 THEN 'Mar'
        WHEN 4 THEN 'Apr'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'Jun'
        WHEN 7 THEN 'Jul'
        WHEN 8 THEN 'Aug'
        WHEN 9 THEN 'Sep'
        WHEN 10 THEN 'Oct'
        WHEN 11 THEN 'Nov'
        WHEN 12 THEN 'Dec'
    END
    SELECT CAST(ROUND(CAST(SUM(t.[E-Mail Average]) AS Decimal) / CAST(COUNT(t.CSRName) AS Decimal), 0, 0) AS Int)
         FROM dbo.fnEMMonthlyAvg(@mon, '08') t
    SELECT @count = @count - 1
END

However, I get the following message:
Msg 156, Level 15, State 1, Procedure EMpsView_vw, Line 5
Incorrect syntax near the keyword 'DECLARE'.

This view is the last in a long line of attempts to get this code working properly. I can get this code to work in the Query Analyzer when not a part of a view, and I get the desired results. Where am I going wrong here? If possible, I'd prefer to do this in a UDF where I can pass the @count value to it, but that was not working either for different reasons. This is super-frustrating. Thanks;
Jim

planoie · March 10th, 2008, 11:12 AM

The T-SQL docs for CREATE VIEW show this as the syntax for a view:

Code:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

This means that you need to use a single SELECT statement as the body of the view. The statement can contain UNIONs.

You can not treat the view body like a stored procedure and do more complex internal processing like you are.

-Peter
peterlanoie.blog

Jimbocephus · March 10th, 2008, 11:20 AM

Peter,
Thanks for the quick reply! Looks like I'm back to trying this out as a UDF, or I may possibly just use the sql query in my VB code to coax the results out and combine it with the related functions. This is part of a whole gang of inter-related functions and views that return specific data to specific vb apps. The query in question here was part of a function that used to work (in a different form...) on one server, but has been problemmatic ever since we migrated to a new server (same version of SQL, different name). It's been nothing but a headache...Thanks again;
Jim

Jeff Moden · March 10th, 2008, 08:59 PM

I'd recommend you lookup "Cross Tab" in Books Online... they way you're trying to do things with UDF's is going to make for some awfully slow code. You might also want to look the functions up in the following code...

Code:

 SELECT LEFT(DATENAME(mm,DATEADD(mm,Number,0)),3)
   FROM Master.dbo.spt_Values WITH (NOLOCK)
  WHERE Type = 'P'
    AND Number BETWEEN 0 AND 11

You might also want to lookup what CTE's are all about... they're very helpful in the constuction of such views.

--Jeff Moden

qazi_nomi · March 11th, 2008, 01:04 AM

For this why don't you using Store Procedures instead of views ?

(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is how you got it

Preethi · March 11th, 2008, 08:01 AM

Try this option
http://www.sqlservercentral.com/arti...ingviews/1178/

I have used something similar.. but using my own table :)

G.R.Preethiviraj Kulasingham

Jimbocephus · March 11th, 2008, 12:56 PM

Everyone,
Thanks for all of the replies! I have decided to split the work up into two different routines: one which collects the individualized info and another that does the aggregate averaging. This way, the functions are very simple and run fast. I then have the VB application run the second function one time for each month, and the collecting array stays the same. The VB re-code was pretty simple, and it all works good as new. I'm still nit sure why my original function (not shown) decided to stop working correctly after we migrated the databases, but cest la vie...
Jim

Jeff Moden · March 11th, 2008, 01:14 PM

Lordy, I know it works and I know you're used to it... I just hate to see folks bog down SQL like that.

--Jeff Moden

Jimbocephus · March 14th, 2008, 02:30 PM

Jeff,
It's actully pretty fast as it only needs to go through about a month worth of scores at a clip, and the VB loop only runs a max of 12 times (once per month, each month runs the previous months in the same calendar year). This report only gets run a couple of times at the end of the month, so it's not a daily thing. Running it and exporting it to Excel takes < 5 seconds combined.
I'm not conversant with the CROSS TABS method you mentioned, but I am going to check it out when I get some time to see what it can do for me in this situation. Thanks for the heads-up, and no worries;
Jim