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.
nuladAsked:
Who is Participating?
 
planoczCommented:
Sorry I was think SP, but still the same in the Function.
0
 
planoczCommented:
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.
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
James0628Commented:
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.

 James
0
 
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 :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_1931-Reporting-On-Data-From-Stored-Procedures-part-1.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2002-Reporting-On-Data-From-Stored-Procedures-part-2.html
0
 
TempDBACommented:
nulad,
     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.
0
 
James0628Commented:
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.

 James
0
 
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 :)
0
 
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?
0
 
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.
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.