3 Added the generic tags of (a) the DBMS of relevance and (b) the corresponding SQL dialect; modified formatting.
source | link

SQL TSQL - intermediateIntermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above    

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.:

-- 3. "usernames() function is created after both of the above".

  1. I drop all the functions from sysobjects

  2. I recreate all the functions again.

  3. "the usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results  ? orOr is it how the dependencies are resolved for UDF and iTVF  ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

SQL - intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.

-- 3. "usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results  ? or is it how the dependencies are resolved for UDF and iTVF  ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above    

This is how I create all the functions for my DB:

  1. I drop all the functions from sysobjects

  2. I recreate all the functions again.

  3. "the usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results? Or is it how the dependencies are resolved for UDF and iTVF?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

2 added 214 characters in body
source | link

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.

-- 3. "usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't have any problems? run into this error.

Is this because how the inline TVF sees the internal results ? or is it how the dependencies are resolved for UDF and iTVF ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.

-- 3. "usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above). But for the UDF function it doesn't have any problems?

Is this because how the inline TVF sees the internal results ? or is it how the dependencies are resolved for UDF and iTVF ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.

-- 3. "usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results ? or is it how the dependencies are resolved for UDF and iTVF ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

1
source | link

SQL - intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500) 
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE 
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB

-- 1. I drop all the functions from sysobjects

-- 2. I recreate all the functions again.

-- 3. "usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above). But for the UDF function it doesn't have any problems?

Is this because how the inline TVF sees the internal results ? or is it how the dependencies are resolved for UDF and iTVF ?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,