Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 10th, 2008, 11:04 AM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using variables in Views

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
Reply With Quote
  #2 (permalink)  
Old March 10th, 2008, 11:12 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 10th, 2008, 11:20 AM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 10th, 2008, 08:59 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 11th, 2008, 01:04 AM
Friend of Wrox
Points: 1,288, Level: 14
Points: 1,288, Level: 14 Points: 1,288, Level: 14 Points: 1,288, Level: 14
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2004
Location: Stockholm, Sweden
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi
Default

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
Reply With Quote
  #6 (permalink)  
Old March 11th, 2008, 08:01 AM
Authorized User
 
Join Date: Jun 2003
Location: Colombo 04, WP, Sri Lanka.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Preethi Send a message via Yahoo to Preethi
Default

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

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

G.R.Preethiviraj Kulasingham
Reply With Quote
  #7 (permalink)  
Old March 11th, 2008, 12:56 PM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old March 11th, 2008, 01:14 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old March 14th, 2008, 02:30 PM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Views prashar SQL Language 1 December 13th, 2005 02:10 AM
converting Access 2000 views to Sql views matta Classic ASP Professional 1 January 26th, 2005 03:37 PM
Variables in views richarda SQL Server 2000 9 December 23rd, 2004 12:29 PM
Views or not ?? stagedancer SQL Server 2000 1 November 24th, 2004 03:08 PM
Views SAM GORDON SQL Server 2000 5 July 19th, 2003 08:14 AM



All times are GMT -4. The time now is 08:49 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.