SQL Server 2008 User Defined Function

I am new to creating user defined functions and need help.

I am trying to create a SQL Server 2008 reusable user defined function that can be used by various SSRS reports.  The SSRS reports need to find the previous Year and Quarter from 2 of the reports’ parameters.

There are four (4) SSRS report parameters:
@ChosenYear  varchar(4)
@ChosenQuarter  varchar(10)
@PrevYr  varchar(4)
@PrevQtr  varchar(10)

If a user selects Quarter “four” from the report’s parameter (@ChosenQuarter),  then the @PrevYr parameter should equal (@ChosenYear – 1) .

If a user selects anything other than Quarter “four” from the report’s parameter (@ChosenQuarter), then the @PrevYr parameter should equal @ChosenYear.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It sounds like the only parameter you need is the ChoseQtr .
The rest of the parameters can be created in the query (or Stored procedure)
Which would produce the dataset to fill the report.
Sorry I was think SP, but still the same in the Function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
I would build a stored procedure, not a function.  Unless you need to join the results of the function with other tables.

From what I can tell, you probably need two parameters: @ChosenQuarter and @ChosenYear.  All the logic that you've mentioned can then be implemented inside the stored proc.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

You use (year - 1) for quarter 4?  I would have thought that you'd subtract 1 for quarter 1.  For example, for Q1 2011, the previous quarter is Q4 2010.  If that's not correct, then maybe you should tell us exactly what values you want returned for each quarter.

 And, FWIW, I agree with the others, in that a function seems like overkill for this.

Mark WillsTopic AdvisorCommented:
If there is some kind of procedual logic needed, then definitely use a Stored Procedure.

The coding for @prev_year can be readily handled and altered over time, and ideally (because you are using a stored procedure and can do date maths easily enough), use date or datetime parameters within the SP if ranging on date type contructs rather than using things like "year(my_date)"

But, you might also be able to use a direct query (as in "case" statement in 2008 and earlier or "iif" statement in Denali) e.g. "when year(my_date) = case when @current_quarter = 4 then @current_year - 1 else @current_year end" but it is not a great example because it would be poor coding choice for performance...

But still, would be inclined to use a stored procedure for your report (almost anytime when there is a possibility of date ranging maths involved - because you can also check for "reasonableness" and block potentially huge queries)

So, check out a couple of Articles by ValentinoV :


     Have you gone through the suggestion? If yes, were they helpful or if no would you mind checking them out as we would know what error\problem you are facing after this.
I'd have to say "delete", since we never got any response to any of our suggestions/questions.  Besides that, I don't see any specific solutions, although I didn't look at the links that Mark posted.

Mark WillsTopic AdvisorCommented:
Well, if we could convince nulad to use a stored procedure instead of a user defined function, then those links are very good examples of how the report could happen using stored procedure.

Cannot really see the need for a user defined function from what is being said. Can use a case statement as per my example, though, not great from a performance perspective.

I think I did provide a viable solution, just not sure if I answered the question :)
ValentinoVBI ConsultantCommented:
Agreed with Mark's comment :)

BTW: looks like nulad is having some time off - latest activity was this question, maybe worth waiting a bit more?
nuladAuthor Commented:
Sorry, thought I left a comment that I would try using a stored procedure.  Currently am using a CASE statement in all the reports, but was hoping to use one function they could all use.  Thnak you for your comments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.