need table returning function with input table name as parameter
-
Hi folks,
I've done some searches on here and on the web, and I'm still confused. I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Can anyone share syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CS\H388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows, so, any pointers on optimization would be great too.
Any help appreciated.
-
You (and your boss) need to define priorities.
If you want flexibility to send the table's name as a parameter, you'll get bad performance.
If you want to get good performance, you can't have the flexibility and you'll need to create a function for each table.
If you want my opinion, I'd use the second option.
-
Thanks Luis.
I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
-
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (11/19/2014)
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
Unless something's changed - you can't even do that with multi-line functions, since you can't run EXEC or sp_executeSQL within a function. As far as I know you're back to stored procs for this.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Yes, looks like I'm back to stored procs.
What I need to do is join some tables and return the results.
But can I use the results of a stored proc in a further query?
-
Matt Miller (#4) (11/19/2014)
Sean Lange (11/19/2014)
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
Unless something's changed - you can't even do that with multi-line functions, since you can't run EXEC or sp_executeSQL within a function. As far as I know you're back to stored procs for this.
Good point...yet another reason not to do something like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
DSNOSPAM (11/19/2014)
Yes, looks like I'm back to stored procs.What I need to do is join some tables and return the results.
But can I use the results of a stored proc in a further query?
You can insert the results of a proc into a table. But here we go down the path of horrendous performance. You would have to read data from a table, then insert it into a temp table just so you can access it again.
The problem lies in that you are trying to create "the one proc to rule them all". This is like creating a method in .NET that can manipulate any object. The language just isn't designed to do this kind of thing.
Thanks to Lowell for the image. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Matt Miller (#4) (11/19/2014)
Sean Lange (11/19/2014)
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
Unless something's changed - you can't even do that with multi-line functions, since you can't run EXEC or sp_executeSQL within a function. As far as I know you're back to stored procs for this.
Technically, it can be done thinking outside of the box. I won't be posting it here, though.
-
Luis Cazares (11/19/2014)
Matt Miller (#4) (11/19/2014)
Sean Lange (11/19/2014)
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
Unless something's changed - you can't even do that with multi-line functions, since you can't run EXEC or sp_executeSQL within a function. As far as I know you're back to stored procs for this.
Technically, it can be done thinking outside of the box. I won't be posting it here, though.
Yes it can be done using a fairly well known kludge but I agree about not posting that...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I have a decoder ring I got from a Cracker Jax box. Will that get me into the inner circle? Come to think of it, that's also where I found a manual for sql server coding.
-
DSNOSPAM (11/19/2014)
I have a decoder ring I got from a Cracker Jax box. Will that get me into the inner circle? Come to think of it, that's also where I found a manual for sql server coding.It isn't really that it is an inner circle. It is that the kludge is extremely ugly and the performance is horrendous. You can force this using OPENQUERY. Beware...this is NOT a good practice and is a recipe for performance woes like you have never experienced before.
Another option would be to use CLR. This also seems like a lot of wasted overhead for something that shouldn't be done in the first place.
To me this is a case of "just because you can do something with sql doesn't mean you should".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
It's not that we don't want to share information because we're jealous. If that were the case, we wouldn't be helping on the forums. The idea of not posting it is because it's a bad solution that we wouldn't recommend.
-
I don't believe "the kludge" allows you to pass parameters to a stored procedure in a function. To do that, I believe that you still need dynamice SQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sean Lange (11/19/2014)
Luis Cazares (11/19/2014)
Matt Miller (#4) (11/19/2014)
Sean Lange (11/19/2014)
DSNOSPAM (11/19/2014)
Thanks Luis.I've been reading about possible performance hits. However, this is in a reporting/analysis environment, not on a production server, and it would be less of a consideration, though the resulting speed would still be an issue if it is very bad.
So, still looking for answers to some of the questions I raised.
It is not exactly clear what you are trying to do. I think you are wanting to pass in the table name and have your function select a column from the table whose name you passed in? This is not good practice at all but you can accomplish it using dynamic sql. Of course as suggested the performance is going to be pretty awful because you can't do this as an inline table valued function. It will have to be a multi statement table valued function which are horrible from a performance perspective.
Unless something's changed - you can't even do that with multi-line functions, since you can't run EXEC or sp_executeSQL within a function. As far as I know you're back to stored procs for this.
Technically, it can be done thinking outside of the box. I won't be posting it here, though.
Yes it can be done using a fairly well known kludge but I agree about not posting that...
You're right I had discarded that option so long ago I never even considered that. In addition to being kludgy I remember there being some form of security whole possibility there as well. Yep - no thanks I will pass on it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply