Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

Question

Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

Newcomsas

SSC Veteran

Points: 293
More actions
July 4, 2014 at 7:17 am

#293059

Hello,
I have got two user-defined functions: fn_Top and fn_Nested. The first one, fn_Top, is structured in this way:
CREATE FUNCTION [dbo].[fn_Top]
RETURNS @Results table (
MyField1 nvarchar(2000),
MyField2 nvarchar(2000)
)
AS
BEGIN
INSERT INTO
@Results
(
MyField1,
MyField2
)
SELECT
item,
(SELECT MyString FROM dbo.fn_Nested(x.MyCounter))
FROM
OtherTable x
RETURN
END
I would like to perform a sort of dynamic parameter passing to the second function, fn_Nested, reading the values of the numeric field MyCounter in the table OtherTable.
Fact is, x.MyCounter is not recognized as a valid value.
Everything works fine, on the other hand, if I set in fn_Nested a static parameter, IE dbo.fn_Nested(17).
Is there anyone who can suggest a workaround to solve this problem ? Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Newcomsas (7/4/2014)
Hello,
I have got two user-defined functions: fn_Top and fn_Nested. The first one, fn_Top, is structured in this way:
CREATE FUNCTION [dbo].[fn_Top]
RETURNS @Results table (
MyField1 nvarchar(2000),
MyField2 nvarchar(2000)
)
AS
BEGIN
INSERT INTO
@Results
(
MyField1,
MyField2
)
SELECT
item,
(SELECT MyString FROM dbo.fn_Nested(x.MyCounter))
FROM
OtherTable x
RETURN
END
I would like to perform a sort of dynamic parameter passing to the second function, fn_Nested, reading the values of the numeric field MyCounter in the table OtherTable.
Fact is, x.MyCounter is not recognized as a valid value.
Everything works fine, on the other hand, if I set in fn_Nested a static parameter, IE dbo.fn_Nested(17).
Is there anyone who can suggest a workaround to solve this problem ? Thanks in advance.

Without the definition of OtherTable and fn_Nested, a quick guess would be to use APPLY: -

INSERT INTO @Results

(

MyField1,

MyField2

)

SELECT item,

nestedFunction.MyString

FROM OtherTable x

OUTER APPLY (

SELECT MyString

FROM dbo.fn_Nested(x.MyCounter)

) nestedFunction;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Newcomsas SSC Veteran Points: 293 More actions · Answer 2

OUTER APPLY (
SELECT MyString
FROM dbo.fn_Nested(x.MyCounter)
) nestedFunction;

Apparently this solution does not work. I receive the ususal error "Incorrect syntax near 'x'.".

And, again, passing a static value everything works correctly: fn_Nested(17), for example, is accepted.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Please post the definition of function dbo.fn_Nested.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

What happens if you run this

SELECT

item,

(SELECT MyString FROM dbo.fn_Nested(x.MyCounter))

FROM

OtherTable x

in a query window?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

ChrisM@Work (7/4/2014)
Please post the definition of function dbo.fn_Nested.

Also the definition of OtherTable please.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/