SQL Function to loop through table

Question

I'm new to tSQL functions and I'm trying to do the following:

I have a table like this called Projects:

+----------+--------------+
| Code     | Previous_Code|
+----------+--------------+
| 001      | NULL         |
| 002      | 001          |
| 003      | 002          |
| 004      | NULL         |
| 005      | NULL         |
+----------+--------------+

And I'd like to create an SQL function to produce the following query result:

+----------+--------------+
| Code     | Original_Code|
+----------+--------------+
| 001      | 001          |
| 002      | 001          |
| 003      | 001          |
| 004      | 004          |
| 005      | 005          |
+----------+--------------+

I've come up with the function below:

CREATE FUNCTION OriginalProjectCode (@Code VARCHAR(3))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @Code2 AS VARCHAR(3);
WHILE @Code IS NOT NULL
    BEGIN
        SET @Code2 = @Code;
        SET @Code = ('SELECT [Previous_Code] FROM 
        Projects WHERE [Code] = ' + @Code);
        SET @Code2 = @Code;
    END;
RETURN @Code2;
END;

But it just seems to be in an infinite loop when it is run. Doe anyone one have any ideas or a better way of achieving the same result? Thanks in advance.

Can you clarify the requirements? From your second table it seems that you want to follow the chain of Previous_Code values until you reach a Code with a Previous_Code of NULL, is that right? So, 003 -> 002 -> 001 -> NULL, therefore "003" has an Original_Code of "001". I'm not clear though why "001" has an Original_Code of "001", when "004" and "005" both have Original_Codes of "NULL" — DaveyDaveDave
– DaveyDaveDave, Commented Aug 21, 2018 at 9:12
How does your code executed the SQL statement? I don't see any sp_execute command. — rcs
– rcs, Commented Aug 21, 2018 at 9:13
This is a really bad idea. Scalar functions in SQL Server can perform awfully, and introducing a WHILE loop into one is a double disaster. What is your actual goal here? You would be far far better off using an inline table-value function. — Thom A
– Thom A ♦, Commented Aug 21, 2018 at 9:15
daveydavedave is right to point that out. The example is edited according now. Thanks. — user2751145
– user2751145, Commented Aug 21, 2018 at 9:15
Isn't this just a hierarchy? A recursive CTE should do it. There's plenty of examples on SO — Mazhar
– Mazhar, Commented Aug 21, 2018 at 9:21

Yogesh Sharma · Accepted Answer · 2018-08-21 09:29:43Z

1

It seems simple case expression would work :

select t.*, (case when Previous_Code is not null 
                  then coalesce(lag(Previous_Code, 1) over (order by code), Previous_Code) 
                  else code
             end) as Original_Code
from table t;

answered Aug 21, 2018 at 9:29

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien_The_Unbeliever Over a year ago

This "works" for up to two levels but fails if, say, 004s Previous_Code is 003, which should presumably mean that that row gets assigned 001 as well, but this code assigns it 002. I think they're looking for something that works more generally

Sahi · Accepted Answer · 2018-08-21 09:53:36Z

0

Hope this helps!!

    CREATE TABLE #Projects(Code VARCHAR(10),Previous_Code VARCHAR(10))

    INSERT INTO #Projects
    SELECT '001',NULL  Union ALL
    SELECT '002','001' Union ALL
    SELECT '003','002' Union ALL
    SELECT '004',NULL  Union ALL
    SELECT '005',NULL 

    DECLARE @id varchar(10)='003'

    ;with cte
    AS
    (
         select code,code as Original_Code,ISNULL(Previous_Code,code)Previous_Code
           from #Projects
           union all
          select c.Code,p.code as Original_Code, p.Previous_Code
           from #Projects p
             join cte c on c.Previous_Code = p.code  

    )

    SELECT code,Original_Code
    from cte 
    WHERE
        1= ( CASE WHEN Previous_Code IS NULL AND code !=Original_Code then 1
            WHEN code=Original_Code and Original_Code=Previous_Code then 1
            ELSE 0 END)
    order by code,Previous_Code


    DROP TABLE #Projects

answered Aug 21, 2018 at 9:53

Sahi

1,4841 gold badge15 silver badges34 bronze badges

1 Comment

user2751145 Over a year ago

Genius! I adapted that and got it to work. Thank you for your help.

Collectives™ on Stack Overflow

SQL Function to loop through table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related