Neutrino Neutrino - 7 months ago 17
SQL Question

Table-valued function - duplicate table declaration

I am using table-valued function in SQL Server to return data either with ties or without on some condition specified.

Now the solution works fine but I do not like the fact that I have to declare the table twice - is there any workaroud? I could probably instead of inserting into

@tmp
table use
@ReturnTable
but that data would have to be deleted once
select top 1
is queried at the end of the function.

RETURNS @ReturnTable TABLE
(
idTable INT,
idOther INT,
name VARCHAR(30)
)
AS
BEGIN
DECLARE @tmp TABLE
(
idTable INT,
idOther INT,
name VARCHAR(30)
)

INSERT INTO @tmp
SELECT idTable,
idOther,
name
FROM SomeTable

IF (some condition)
BEGIN
INSERT INTO @ReturnTable
SELECT TOP 1 WITH TIES idTable,
idOther,
name
FROM @tmp
ORDER BY (some ordering)
END;
ELSE
BEGIN
INSERT INTO @ReturnTable
SELECT TOP 1 idTable,
idOther,
name
FROM @tmp
ORDER BY (some other ordering)
END;
RETURN;
END;

Answer

Try re-writing your function to Inline table valued function.

Based on the condition you have to use either ROW_NUMBER or DENSE_RANK window function. TOP 1 with TIES can be achieved using DENSE_RANK/RANK

RETURNS TABLE 
AS 
    RETURN 
      (SELECT idtable, 
              idother, 
              NAME 
       FROM   (SELECT CASE 
                        WHEN (some condition) THEN Dense_rank()OVER(ORDER BY (some ordering)) 
                        ELSE Row_number()OVER(ORDER BY (some other ordering)) 
                      END     AS RN, 
                      idtable,
                      idOther, 
                      NAME 
               FROM   sometable)a 
       WHERE  RN = 1)