Alternative to variables for a View

Question

Alternative to variables for a View

cidr

Ten Centuries

Points: 1353
More actions
August 10, 2009 at 7:09 am

#74086

Hi there,
I've been working on a query for a while to do with budgets.
As some code requires a GROUP BY, I've created a view for part of that code because the main query has a subquery in the select list and this isn't allowed with GROUP BY.
However, the code within the View originally had parameters. It seems paramters or declaring them isn't allowed in a view. The paramters are for dates. The dates will always be from the 1st of Febuary of the year a report is run to date.
datAuditDate >= @StartDate and
datAuditDate< @EndDate -- could use getdate() Does anyone know if there's a way round this for a view? perhaps calling a function or something? Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Simon Liddle SSCrazy Points: 2580 More actions · Answer 1

Did you read the article Gail Shaw (GilaMonster) linked in reply to your other post - you'll get a much stronger response and far greater help if you follow its guides to helping others help you. 🙂

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

I'm not sure why you've created a view but if you could post up the details we might be able to point you in the right direction.

cidr Ten Centuries Points: 1353 More actions · Answer 2

Hi there,

I've not read the article yet, and yes, I agree that I should provide code and samples. it's a little tricky with this one cause there's so many tables with subqueries involved I'd struggle to get something across.

I scrapped the view and just used a subquery in the From clause. However, because there are aggregates in there I get a SQL server internal error.

I'll leave this one now and do a work around.

In future I'll be more explicit.

Cheers

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 3

Here is a quick trick to get this... I am pretty sure it will work in SQL 7/2000:

DATEADD(yy, YEAR(GETDATE()) - 2000, '2000-02-01')

You can use this trick to get any date for the current year.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Typically if you have a View that you need to pass parameters to, you would create a table-valued function. You should be okay with that performance-wise as long as it is in the FROM clause and not the select list.

FYI, as far as I know you can do a derived table (sub-query in FROM clause) that includes a aggregation and GROUP BY.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question