This will be both a direct question and a debatable question. First I will ask a direct question:
Can a stored procedure dynamically create another stored procedure? (Personally, I'm interested in SQL Server 2008, but in the interest of a broader discussion, leave it open)
Now about the reason I'm asking. In short (you can read more elsewhere), user-defined scalar functions in SQL Server are performance bottlenecks, at best. I saw the use of code in our database that slows down the general query by 3-4 times, but from what I read, the local impact of S-UDF can be 10x +
However, UDFs are potentially great for raising levels of abstraction, reducing the number of tedious patterns, centralizing logical rules, etc. In most cases, they come down to simple expressions that can easily be extended by inline ones, but they are not (I "In fact, we only think about queries not related to queries", for example, string manipulations). I saw a bug report that will be covered in the next release - with some buy-in from MS. But for now, we have to live with broken implementation (IMHO).
One solution is to use the value of the UDF table instead - however, this complicates the client code in ways that you do not always want to deal with (for example, when UDF just calculates the result of an expression).
So, my crazy idea was to write procs with C Preprocessor directives, and then pass it through the preprocessor before sending it to RDBMS. This may work, but has its problems.
This led me to my next crazy idea, which was to define βmacrosβ in the database itself and have a master process that takes a string containing an unprocessed SP with macros, extends the inline macros and then sends them to RDMS. It's not that SPs are good, but I think it might work - assuming you can do it first - hence my original question.
, . , , . , ? googling .
, .
[]
, , , . , - , , , , .
, WITH SCHEMABINDING S-UDF, , ,