October 10, 2014 at 2:47 am
I have the following "dummy" function:
IF OBJECT_ID('FN_REPLACE', N'IF') IS NOT NULL
DROP FUNCTION FN_REPLACE
GO
CREATE FUNCTION dbo.FN_REPLACE
(
@pString NVARCHAR(MAX)
,@pReplaceSegment NVARCHAR(MAX)
,@pReplaceWith NVARCHAR(MAX)
)
RETURNS TABLE AS
RETURNSELECTREPLACE(@pString, @pReplaceSegment, @pReplaceWith) AS replaced_string
GO
And i try to nest calls to the function like following:
SELECT*
FROMdbo.FN_REPLACE(
(
SELECT*
FROMdbo.FN_REPLACE('Testing string', 'Test', '')
)
,'String'
,''
) x
But this raises error in Microsoft SQL Server 2012 - 11.0.2100.60 (X64):
Msg 4429, Level 16, State 1, Line 2
View or function 'dbo.FN_REPLACE' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
Is this a limitation of Inline-Functions or is there a way to make nested functions work?
October 10, 2014 at 2:56 am
Deleted comment
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 10, 2014 at 6:22 am
You're not simply nesting the functions, you're self-referencing. That's very different, and, as you're seeing in the error message, not allowed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2014 at 7:17 am
I don't actually reference the function inside itself.
Script #2 is what i try to run outside of the created function body.
The function definition is still the same as in Script #1, the whole script is therefore:
IF OBJECT_ID('FN_REPLACE', N'IF') IS NOT NULL
DROP FUNCTION FN_REPLACE
GO
CREATE FUNCTION dbo.FN_REPLACE
(
@pString NVARCHAR(MAX)
,@pReplaceSegment NVARCHAR(MAX)
,@pReplaceWith NVARCHAR(MAX)
)
RETURNS TABLE AS
RETURNSELECTREPLACE(@pString, @pReplaceSegment, @pReplaceWith) AS replaced_string
GO
SELECT*
FROMdbo.FN_REPLACE(
(
SELECT*
FROMdbo.FN_REPLACE('Testing string', 'Test', '')
)
,'String'
,''
) x
GO
October 10, 2014 at 7:24 am
The syntax of your query might work for scalar functions, but it's incorrect for table-valued functions. Try this instead:
SELECT *
FROM dbo.FN_REPLACE('Testing string', 'Test', '') x1
CROSS APPLY dbo.FN_REPLACE(x1.replaced_string,'String','')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2014 at 7:30 am
Nah, it works fine, if the function name in the "nest" is different:
IF OBJECT_ID('FN_REPLACE', N'IF') IS NOT NULL
DROP FUNCTION FN_REPLACE
GO
IF OBJECT_ID('FN_REPLACE2', N'IF') IS NOT NULL
DROP FUNCTION FN_REPLACE2
GO
CREATE FUNCTION dbo.FN_REPLACE
(
@pString NVARCHAR(MAX)
,@pReplaceSegment NVARCHAR(MAX)
,@pReplaceWith NVARCHAR(MAX)
)
RETURNS TABLE AS
RETURNSELECTREPLACE(@pString, @pReplaceSegment, @pReplaceWith) AS replaced_string
GO
CREATE FUNCTION dbo.FN_REPLACE2
(
@pString NVARCHAR(MAX)
,@pReplaceSegment NVARCHAR(MAX)
,@pReplaceWith NVARCHAR(MAX)
)
RETURNS TABLE AS
RETURNSELECTREPLACE(@pString, @pReplaceSegment, @pReplaceWith) AS replaced_string
GO
SELECT*
FROMdbo.FN_REPLACE(
(
SELECT*
FROMdbo.FN_REPLACE2('Testing string', 'Test', '') -- FN_REPLACE2 works fine
)
,'String'
,''
) x
GO
October 10, 2014 at 7:47 am
Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying to pass as the first parameter to your outer function. That is why you can't "nest" them. You would need to create a user defined table type and make that both the inbound and outbound datatype to do this like you are trying to do.
I was going to suggest using cross apply but I see that Chris already posted that suggestion. That would be a lot cleaner than creating a duplicate of your function.
_______________________________________________________________
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/
October 10, 2014 at 8:22 am
Sean Lange (10/10/2014)
Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying to pass as the first parameter to your outer function. That is why you can't "nest" them. You would need to create a user defined table type and make that both the inbound and outbound datatype to do this like you are trying to do.I was going to suggest using cross apply but I see that Chris already posted that suggestion. That would be a lot cleaner than creating a duplicate of your function.
Or simply make the function a scalar function. The nesting of scalar functions wouldn't be causing you any headaches.
----------------------------------------------------------------------------------
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?
October 10, 2014 at 8:28 am
Matt Miller (#4) (10/10/2014)
Sean Lange (10/10/2014)
Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying to pass as the first parameter to your outer function. That is why you can't "nest" them. You would need to create a user defined table type and make that both the inbound and outbound datatype to do this like you are trying to do.I was going to suggest using cross apply but I see that Chris already posted that suggestion. That would be a lot cleaner than creating a duplicate of your function.
Or simply make the function a scalar function. The nesting of scalar functions wouldn't be causing you any headaches.
No headaches other than the awful performance. 😉
_______________________________________________________________
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/
October 10, 2014 at 8:55 am
Sean Lange (10/10/2014)
Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying to pass as the first parameter to your outer function. That is why you can't "nest" them. You would need to create a user defined table type and make that both the inbound and outbound datatype to do this like you are trying to do.
Yes, that's what i thought to, but originally my query was just trying to select this value as a correlated subquery or whatever the name for this thing is, something like:
SELECT'123', (SELECT TOP 1 y.replaced_string FROM dbo.FN_REPLACE(
(SELECT (
SELECTTOP 1 x.replaced_string
FROMdbo.FN_REPLACE('Testing string', 'Test', '') x
)
)
,'String'
,''
) y)
Of course this is a convoluted example, but it shows that i "convert" whatever is returned from FN_REPLACE into a regular field in both places.
I also added all aliases that might be needed and removed '*' so there is absolutely no doubt that it just fetches a single field.
From my understanding of SQL syntax, this code should "convert" whatever is returned from FN_REPLACE into a scalar value, and i see no reason for this syntax not to work, that's why i'm asking if this is a bug. I agree with everyone that this is not a good solution AND looks ugly as hell, but i got hit by this issue today, so wanted to check if this is an actual bug/limitation of SQL Server or "buggy" code on my part.
October 10, 2014 at 9:06 am
siggemannen (10/10/2014)
Sean Lange (10/10/2014)
Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying to pass as the first parameter to your outer function. That is why you can't "nest" them. You would need to create a user defined table type and make that both the inbound and outbound datatype to do this like you are trying to do.Yes, that's what i thought to, but originally my query was just trying to select this value as a correlated subquery or whatever the name for this thing is, something like:
SELECT'123', (SELECT TOP 1 y.replaced_string FROM dbo.FN_REPLACE(
(SELECT (
SELECTTOP 1 x.replaced_string
FROMdbo.FN_REPLACE('Testing string', 'Test', '') x
)
)
,'String'
,''
) y)
Of course this is a convoluted example, but it shows that i "convert" whatever is returned from FN_REPLACE into a regular field in both places.
I also added all aliases that might be needed and removed '*' so there is absolutely no doubt that it just fetches a single field.
From my understanding of SQL syntax, this code should "convert" whatever is returned from FN_REPLACE into a scalar value, and i see no reason for this syntax not to work, that's why i'm asking if this is a bug. I agree with everyone that this is not a good solution AND looks ugly as hell, but i got hit by this issue today, so wanted to check if this is an actual bug/limitation of SQL Server or "buggy" code on my part.
It really isn't a bug or anything like that. You are trying to force a square peg in a round hole. By that I mean you are trying to treat a table valued function like a scalar function. They are not the same beast. Did you take a look at using cross apply like Chris posted? It works perfectly and that is by far the best way to deal with this type of thing.
Just for completeness sake here is the entire package. Using your original create function code and a slight modification to Chris' code to only return the final value.
IF OBJECT_ID('FN_REPLACE', N'IF') IS NOT NULL
DROP FUNCTION FN_REPLACE
GO
CREATE FUNCTION dbo.FN_REPLACE
(
@pString NVARCHAR(MAX)
,@pReplaceSegment NVARCHAR(MAX)
,@pReplaceWith NVARCHAR(MAX)
)
RETURNS TABLE AS
RETURNSELECTREPLACE(@pString, @pReplaceSegment, @pReplaceWith) AS replaced_string
GO
SELECT x2.*
FROM dbo.FN_REPLACE('Testing string', 'Test', '') x1
CROSS APPLY dbo.FN_REPLACE(x1.replaced_string,'String','') x2
_______________________________________________________________
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/
October 10, 2014 at 10:22 am
Hi,
Yes, i saw the solution. The reason i'm stubborn is that it works fine if i call different inline TVFs inside each other, as i demonstrated with my second script.
If my syntax is incorrect, i should then be given an error there as well.
But anyway, i will use the CROSS APPLY syntax so we can "close" this issue 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply