4

I'm trying to create a set of data that I'm going to write out to a file, it's essentially a report composed of various fields from a number of different tables, some columns need to have some processing done on them, some can just be selected.

Different users will likely want different processing performed on certain columns, and in the future, I'll probably need to add additional functions for computed columns.

I'm considering the cleanest/most flexable approach to storing and using all the different functions I'm likely to need for these computed columns, I've got two ideas in my head, but I'm hoping there might be a much more obvious solution I'm missing.

For a simple, slightly odd example, a Staff table:

Employee |    DOB           |   VacationDays
Frank    |    01/01/1970    |   25
Mike     |    03/03/1975    |   24
Dave     |    05/02/1980    |   30

I'm thinking I'd either end up with a query like

SELECT NameFunction(Employee, optionID),
       DOBFunction(DOB, optionID),
       VacationFunction(VacationDays, optionID),
from Employee

With user defined functions, where the optionID would be used in a case statement inside the functions to decide what processing to perform.

Or I'd want to make the way the data is returned customisable using a lookup table of other functions:

ID  |    Name                  |   Description
1   |    ShortName             |   Obtains 3 letter abbreviation of employee name
2   |    LongDOB               |   Returns DOB in format ~ 1st January 1970
3   |    TimeStampDOB          |   Returns Timestamp for DOB
4   |    VacationSeconds       |   Returns Seconds of vaction time
5   |    VacationBusinessHours |   Returns number of business hours of vacation

Which seems neater, but I'm not sure how I'd formulate the query, presumably using dynamic SQL? Is there a sensible alternative?

The functions will be used on a few thousand rows.

The closest answer I've found was in this thread: Call dynamic function name in SQL

I'm not a huge fan of dynamic SQL, although in this case I think it might be the best way to get the result I'm after?

Any replies appreciated, Thanks, Chris

Community
  • 1
  • 1
Chris
  • 8,268
  • 3
  • 33
  • 46

3 Answers3

1

I would go for the second solution. You could even use real stored proc names in your lookup table. 

create proc ShortName (
    @param varchar(50)
)as
begin
    select 'ShortName: ' + @param
end
go

declare @proc sysname = 'ShortName'

exec @proc 'David'

As you can see in the example above, the first parameter of exec (i.e. the procedure name) can be a parameter. This said with all the usual warnings regarding dynamic sql...

David Brabant
  • 41,623
  • 16
  • 83
  • 111
  • Thanks for the example, I'm going to create a few test tables/functions to experiment with and examine my results. Thanks! (would upvote if I had the rep). – Chris May 31 '12 at 08:47
1

In the end, you should go with whichever is faster, so you should try both ways (and any other way someone might come up with) and decide after that.

I like better the first option, as long as your functions don't have extra selects to a table. You may not even need the user defined functions, if they are not going to be reused in a different report.

I prefer to use Dynamic SQL ony to improve a query's performance, such as adding a dynamic ordering or adding / removing complex WHERE conditions.

But these are all subjective opinions, the best thing is try, compare, and decide.

Alejandro B.
  • 4,807
  • 2
  • 33
  • 61
  • Well thankfully I'll probably be creating the functions in either case, and adding the lookup table as a layer above them so it shouldn't be too much hassle to quickly test the first alternative. Thanks (haven't got enough rep to upvote yet). – Chris May 31 '12 at 08:42
1

Actually, this isn't a question of what's faster. It is a question of what makes the code cleaner, particularly for adding new functionality (new columns, new column formats, re-ordering them).

Don't think of your second approach as "using dynamic SQL", since that tends to have negative connotations. Instead, think of it as a data-driven approach. You want to build a table that describes the columns that users can get, and the formats. This is great! Users can then provide a list of columns, and you'll have a magical stored procedure that combines the information from the users with the information in your metadata table, and produces the desired result.

I'm a big fan of data-driven approaches, and dynamic SQL is the best SQL tool I've found so far for implementing them.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the reply, you're bang on with what I'm trying to achieve, flexability and readability (so the next person doesn't have to do the same untangling when they want to alter things). I'll see how I get on! (again, would upvote but still getting rep). – Chris May 31 '12 at 08:46