February 8, 2010 at 11:42 am
Hi All - I have a question regarding TVF's. I have a requirement to provide a TVF that has two required and three optional parameters. My problem is with the optional params. I am trying to keep this as clean as possible. One ugly method I could use would be to generate the SQL based on a check of each parameter. If I'm not mistaken, there would be 8 possible scenarios. Since I need to return a table, I would like to create a SPROC possibly using dynamic SQL however I am finding I can't call a SPROC in the TVF. Any thoughts or best practices would be greatly appreciated.
February 8, 2010 at 3:48 pm
Not quite clear on the nature of your question. Are you wondering technically how to do it or how should you do it?
The method for creating optional parameters in a table-valued function is simple: in the declaration of the table, include default values for the function:
CREATE FUNCTION test(@foo VARCHAR(3) = 'bar'...
You then call the code using the keyword DEFAULT in place of the optional parameters:
SELECT * FROM test(DEFAULT) --will use default value 'bar' for parameter foo
How you _should_ approach the task is another question. Sometimes the task at hand fits nicely into the framework, and sometimes it doesn't.
An example that I frequently use is one where the optional parameter represents an optional filter. Using a default value is nice, because the user doesn't need to remember what the special value is that returns all records:
CREATE FUNCTION selectValues
(
@filterParam INT = NULL
)
RETURNS TABLE
AS RETURNSELECT valCol
FROM tbl
WHERE filterCol = ISNULL(@filterParam, filterCol)--*
What are you trying to do?
Edit:* just a footnote for people who might stumble into this thread: this won't work if your column holds nulls.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 9, 2010 at 10:51 am
Thank you very much for the reply. Sometimes it takes another set of eyes to see something so obvious. Your solution worked great. Basically I wanted a single query that would handle the optional parameters and return a table. Fortunately my three optional parameter field values don't allow nulls so this works for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply