1

Good Morning.

I have a T-SQL Script which has been developed to execute a Function1 (which itself calls another Function2). Unfortunately I do not have permission to execute these functions. Hence I am trying to re-write the Script to enable the core logic of these functions to be incorporated in to it.

Function1 takes TWO parameters, and returns a Table

CREATE FUNCTION [dbo].[Function1] (@Param1 int, @Param2 varchar)

RETURNS TABLE

AS

RETURN

(

WITH CTE_O AS

(SELECT F1 AS Field_O

FROM Table1

WHERE ODGUID = @Param1

)

SELECT *

FROM dbo.Function2 (@Param1, @Param2, (SELECT TOP (1) Field_O FROM CTE_O) )

);

  1. Function2 takes THREE parameters (two of which are exactly the same two parameters required by Function1). Function2 returns a Table which it passes back to Function1:

CREATE FUNCTION [dbo].[Function2]

(

@Param1 int,

@Param2 varchar,

@Field1 int

)

RETURNS TABLE

AS

RETURN

Kindly advise on a suitable structure to implement these two functions within my current Script.

2

hi

hope this helps :winking_face_with_tongue:

This avoids scalar variables entirely and keeps everything set-based.

SELECT *
FROM SomeTable S
CROSS APPLY
(
    SELECT TOP (1)
           F1 AS Field_O
    FROM Table1
    WHERE ODGUID = @Param1
) O
WHERE S.Field1 = O.Field_O;

Equivalent structure using a CTE:

DECLARE @Param1 INT = 100;
DECLARE @Param2 VARCHAR(50) = 'ABC';

;WITH CTE_O AS
(
    SELECT TOP (1)
           F1 AS Field_O
    FROM Table1
    WHERE ODGUID = @Param1
)
SELECT
       ...
FROM SomeTable ST
JOIN OtherTable OT
    ON ST.ID = OT.ID
CROSS JOIN CTE_O O
WHERE ST.Col1 = @Param1
  AND ST.Col2 = @Param2
  AND ST.Field1 = O.Field_O;