CODEWITHSUNDEEP
sqlsql-serverfunctionloopst-sql
user2751145
user2751145

Reputation: 13

SQL Function to loop through table

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.

Upvotes: 0

Views: 1787

Answers (2)

Sahi
Sahi

Reputation: 1484

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Related Questions