September 14, 2015 at 7:44 am
Guys - I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.
What's the best way to do this ?
Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @szSQL AS VARCHAR(4000)
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
-- SELECT * FROM dbo.UtilTableFromString(@TABLES1)
SET @szSQL =
CASE
WHEN @whichgroup = 1 THEN
'INSERT '+ @RETTAB + ' SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
WHEN @whichgroup = 2 THEN
'INSERT '+ @RETTAB + ' SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
END
exec spExecuteSQL @szSQL
RETURN
END
September 14, 2015 at 8:00 am
This seems somewhat long-winded and will not scale well (because it's not an iTVF).
Can you give a bit of background about what you are trying to achieve & what the desired output should look like? There may be a better way.
If you could provide sample data too (see link in my signature), you'd almost certainly get a working solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2015 at 8:28 am
A function can't insert into a table. I presume that is the error that you are encountering.
A Stored Procedure could do.
The Function could return the SQL, for the code to then execute, or it could return the Tabales Names (much as your UtilTableFromString function does) which the code could use as part of an INSERT INTO MyTable command (assuming that the Code, at that point, knows the name of the table - rather than only having the table name in a @RETTAB variable)
September 14, 2015 at 8:34 am
Kristen-173977 (9/14/2015)
A function can't insert into a table. I presume that is the error that you are encountering.A Stored Procedure could do.
The Function could return the SQL, for the code to then execute, or it could return the Tabales Names (much as your UtilTableFromString function does) which the code could use as part of an INSERT INTO MyTable command (assuming that the Code, at that point, knows the name of the table - rather than only having the table name in a @RETTAB variable)
This is not correct. They can insert into local table variables, which are then returned by the function. See here for an example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2015 at 8:44 am
mar.ko (9/14/2015)
Guys - I have a function that returns a table from a comma-delimited string.I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.
What's the best way to do this ?
Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @szSQL AS VARCHAR(4000)
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
-- SELECT * FROM dbo.UtilTableFromString(@TABLES1)
SET @szSQL =
CASE
WHEN @whichgroup = 1 THEN
'INSERT '+ @RETTAB + ' SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
WHEN @whichgroup = 2 THEN
'INSERT '+ @RETTAB + ' SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
END
exec spExecuteSQL @szSQL
RETURN
END
There are a number of issues here. First, your dynamic sql is incorrect. You are trying to concatenate a string with a table variable. If you are going to use dynamic sql you will need to use a temp table. A table variable declared locally will not be available to the dynamic sql but a temp table would.
As of right now you have the exact same code for each path through the case expression.
Your function is very likely to be a performance timebomb. Multi statement table valued functions are often even worse than scalar functions. Then you are going to have problems with getting a decent execution plan from this because you have dynamic sql in a table valued function. There is no way for the optimizer to get a reasonable row count and it will use cached execution plans that may or may not be efficient for the current query.
I would also be interested to see what your UtilTableFromString function looks like.
It is not totally clear to me what you are trying to do but it absolutely can be done, and efficiently. We just need to understand what you trying to do.
_______________________________________________________________
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/
September 14, 2015 at 9:15 am
Thanks Sean, but whe I try to use a temp table, I get:
"Msg 2772 - Cannot access temporary tables from within a function."
What I am trying to do is develop a function whereby a pass a number and then I get a set of tablenames returned as a table.
Then I'll use that reference in another procedure to iterate the table list and do whatever.
The tablenames are stored as a comma-delimited string....and there will be several sets of them. Each set corresponds to a number which is the parameter value.
I already have a routine to convert the comma delimited string to a table of values.
I'm really struggling with the limitations on tables that are returned from functions....which I think is a very powerful feature, yet difficult to implement.
September 14, 2015 at 9:28 am
Phil Parkin (9/14/2015)
This is not correct. They can insert into local table variables, which are then returned by the function. See here for an example.
Thanks. I wasn't specific 🙂 Are you referring to a Table Value Function's table? or something more crafty that I have missed perhaps?
O/P has this code, i.e. using "any desired table" as the target, which I don't think can be done in a function?
@szSQL = ... 'INSERT '+ @RETTAB + ' SELECT * ...'
exec spExecuteSQL @szSQL
September 14, 2015 at 9:29 am
This compiled but won't execute:
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.
ALTER FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @szSQL AS VARCHAR(4000)
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
SET @szSQL =
CASE
WHEN @whichgroup = 1 THEN
'INSERT INTO @RETTAB SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
WHEN @whichgroup = 2 THEN
'INSERT INTO @RETTAB SELECT * FROM dbo.UtilTableFromString(' + @TABLES2 + ')'
END
exec sp_ExecuteSQL @szSQL
RETURN
END
September 14, 2015 at 9:31 am
mar.ko (9/14/2015)
What I am trying to do is develop a function whereby a pass a number and then I get a set of tablenames returned as a table.
A Table Valued Function could return the list of tables (in your selected Group), but I don't think that the function, itself, can insert them into a table (the name of which you have passed as a parameter)
September 14, 2015 at 9:35 am
mar.ko (9/14/2015)
Thanks Sean, but whe I try to use a temp table, I get:"Msg 2772 - Cannot access temporary tables from within a function."
This is because you really shouldn't be doing this via function. I forgot about that but I wouldn't use a temp table or table variable within a function.
What I am trying to do is develop a function whereby a pass a number and then I get a set of tablenames returned as a table.
Then I'll use that reference in another procedure to iterate the table list and do whatever.
The tablenames are stored as a comma-delimited string....and there will be several sets of them. Each set corresponds to a number which is the parameter value.
Storing delimited lists is a very bad design decision. It violates 1NF and causes a lot of pain to get the data back as you are realizing.
I already have a routine to convert the comma delimited string to a table of values.
Yes you said that. I said I was curious to see it because there are a LOT of way to do this and so many of the ones found around the internet are just awful. Is that an inline table valued function or does it contain a number of statements? Is there a while loop or cursor in it?
To be honest I would consider rethinking this whole process. Instead of cramming all this data into a single column which is difficult to work with why not use a table instead? It is easier to retrieve the data and it is easier to maintain. You don't need to split strings and go through all these gyrations.
create table GroupTables
(
GroupID int not null,
TableName sysname not null
)
insert GroupTables(GroupID, TableName)
select 1, 'CONV_HRLY_RPT_CONV_ADDONS_TBL' union all
select 1, 'CONV_HRLY_RPT_CONV_ATTACH_TBL' union all
select 1, 'CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL' union all
select 1, 'CONV_HRLY_RPT_CONVERSION_TBL' union all
select 1, 'CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL' union all
select 1, 'CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL' union all
select 1, 'CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL' union all
select 1, 'CONV_HRLY_RPT_SALESBYHOUR_TBL'
Now it is super easy to work with. You create a table valued function that receives GroupID and it will return a table contain the values you want in your other process.
_______________________________________________________________
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/
September 14, 2015 at 9:35 am
Phil Parkin (9/14/2015)
This is not correct. They can insert into local table variables, which are then returned by the function.
Sorry, I mistook @RETABLE as a parameter to the Function, containing the name of an external table, rather than it being the Table value Function's returning table.
All clearer now I've reread the post
Perhaps this would do the trick:
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
INSERT INTO @RETTAB
SELECTYourSplitterColumnName
FROMdbo.UtilTableFromString
(
CASE WHEN @whichgroup = 1 THEN @TABLES1
WHEN @whichgroup = 2 THEN @TABLES2
END
)
RETURN
END
September 14, 2015 at 9:42 am
Kristen-173977 (9/14/2015)
Phil Parkin (9/14/2015)
This is not correct. They can insert into local table variables, which are then returned by the function.Sorry, I mistook @RETABLE as a parameter to the Function, containing the name of an external table, rather than it being the Table value Function's returning table.
All clearer now I've reread the post
Perhaps this would do the trick:
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
INSERT INTO @RETTAB
SELECTYourSplitterColumnName
FROMdbo.UtilTableFromString
(
CASE WHEN @whichgroup = 1 THEN @TABLES1
WHEN @whichgroup = 2 THEN @TABLES2
END
)
RETURN
END
🙂 Excellent. Saves me from typing out the explanation!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2015 at 9:53 am
mar.ko (9/14/2015)
This compiled but won't execute:Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.
ALTER FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
)
AS
BEGIN
DECLARE @szSQL AS VARCHAR(4000)
DECLARE @TABLES1 AS VARCHAR(4000)='CONV_HRLY_RPT_CONV_ADDONS_TBL,CONV_HRLY_RPT_CONV_ATTACH_TBL,CONV_HRLY_RPT_CONV_TOTALUPSELL_TBL,CONV_HRLY_RPT_CONVERSION_TBL,CONV_HRLY_RPT_FIVE9_COREPERFORM_TBL,CONV_HRLY_RPT_FIVE9_UPSELLPERFORM_TBL,CONV_HRLY_RPT_FIVE9_SALESFORCE_TBL,CONV_HRLY_RPT_SALESBYHOUR_TBL'
DECLARE @TABLES2 AS VARCHAR(4000)=''
SET @szSQL =
CASE
WHEN @whichgroup = 1 THEN
'INSERT INTO @RETTAB SELECT * FROM dbo.UtilTableFromString(' + @TABLES1 + ')'
WHEN @whichgroup = 2 THEN
'INSERT INTO @RETTAB SELECT * FROM dbo.UtilTableFromString(' + @TABLES2 + ')'
END
exec sp_ExecuteSQL @szSQL
RETURN
END
It's complaining about your use of dynamic SQL. sp_executeSQL isn't allowed within a function (or any other procedure which isn't flagged as an extended store procedure).
Use the solution Kristen showed above - probably what you're looking for.
----------------------------------------------------------------------------------
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?
September 14, 2015 at 10:37 am
Fair enough Sean, thanks for that idea.
However, I don't agree that using comma-delimited lists violates any rules of relation theory.
A list is a list....whether it's in table form or a string.
Also note how much more code is involved in building the list with standard SQL.
Mine was one line of code to describe the list and one line of code to convert it to a table via the function call !
September 14, 2015 at 10:44 am
mar.ko (9/14/2015)
Fair enough Sean, thanks for that idea.However, I don't agree that using comma-delimited lists violates any rules of relation theory.
A list is a list....whether it's in table form or a string.
Also note how much more code is involved in building the list with standard SQL.
Mine was one line of code to describe the list and one line of code to convert it to a table via the function call !
Sean's assumption, I think, is that you are storing the comma-delimited list in a single database column. If that's the case, it's certainly a violation. But if the comma-delimited string is pulled together in another way, there may be no violation.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply