caching output of a user defined function (ms sql server)

I learned today that user defined functions in MS SQL Server have to be deterministic - that is, you can't INSERT or UPDATE or anything that would change the environment.

So if you have a function that takes three parameters, and does a ton of slow-burning table work with them, the obvious thing might be to cache those results somewhere. Have the function return the cached value if it's recent enough, and do the slow-burning table work if it absolutely has to.

But since I can't INSERT from inside the UDF, I can't cache my results. I'm about to write this cache into my app, but is there really no way to accomplish the same thing using SQL?

UPDATE

Thanks, comments!

I'd like to use the result of the function as query criteria. So SPs won't do.

Specifically, I have a function that counts the number of "notes" in a system, based on the product, status, date, etc. Lots of parameters, but the results don't change all that often. Or I have a function that takes a string and returns a table based on some calculations - easy to do onc开发者_如何学Pythone, and never changes.

---

You can't do insert/update in functions as you stated, so I think a different approach is in order. Why don't you make a SP(stored procedure) of the logic you need to do (with all the parameters you need to pass in) and then cache that. This SP would also have an OUTPUT parameter, which would return your the number of 'notes'.

Then, whereever you need to call the function, you make a SP for those queries. Now, in the SP, you can call the previous SP you made, and have it return the value in a variable, that you can use as a return on any of your queries....

Check this out for the OUTPUT parameter syntax (if you don't know already): http://msdn.microsoft.com/en-us/library/ms378108(v=sql.90).aspx