How to use Stored Procedure Select a value using while

Question

I have a SQL table name category.here is the structure.

slno      Category            Uppercategory

1           Mouse              Computer

2          Computer            Electronics

3          Electronics           END

4           END                    -

Here the mouse in the uppercategory Computer and then the Computer in the uppercategory Electronics.and the Electronics is the last uppercategory because the Electronics having the uppercategory ENd.I need to get the last category(Electronics)which the uppercatergory is END. I tried some code but not get the result.here is my code.

USE [Database1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[kt_category]
       @Dcat AS NVARCHAR(250)
AS
DECLARE @tmp TABLE (cater NVARCHAR(255))

BEGIN 

INSERT INTO @tmp 
SELECT UPPERCATEGORY FROM CATEGORY where CATEGORY=@Dcat

while( SELECT UPPERCATEGORY FROM CATEGORY )= 'END'
RETURN
END

Answer 1

You can also use a recursive query to achieve this.

;WITH R
     AS (SELECT *,
                1 AS Level
         FROM   CATEGORY
         WHERE  Category = @Dcat
         UNION ALL
         SELECT C.*,
                R.Level + 1
         FROM   CATEGORY C
                JOIN R
                  ON R.Uppercategory = C.Category)
SELECT TOP 1 *
FROM   R
ORDER  BY Level DESC 

You could also use

SELECT TOP 1 *
FROM   R
WHERE Uppercategory  = 'End'
ORDER  BY Level

as the final query. This has closer semantics to the code in your question.