Select missing number from a column using while loop in SQL Server

nedash

SOLVED CLOSED ACTIVE ANSWERED
User: nedash
Posted: on May 04, 2017 06:14 AM
Forum: SQL Server
Answer: 1
Views: 5256

this is Estate_info table in database:

id	Code
1	10
2	11
3	12
4	13
5	14

and I put lable in page that will bind Code from table in lable below are codes:

SP:

alter procedure [dbo].[ViewCode]
as
begin
select(select Top 1 code+1
from Estate_info  
order by date desc) as code
end

    private void Viewcode()
    {
        using (SqlConnection conn = General.GetConnection())
        {
            using (SqlCommand _cmd = General.GetCommand("ViewCode", conn))
            {
           
                conn.Open();
                SqlDataReader _dr = _cmd.ExecuteReader();
                while (_dr.Read())
                {
                    Lblcode.Text = _dr["code"].ToString();
                }
            }
        }
    }

now according table it will dispaly:

Lblcode.text==> 15 (Top 1 code+1)...

now here I want if I delete rows from Estate_info Like below:

id	code
1	10
3	12
4	13
5	14

here I delete row with id=2 so code:11 isn't in table...

now I want at first check table if all number(codes number) be in table after that it does :(select(select Top 1 code+1 from Estate_info order by date desc) as code

if like above table number wasn't in table it will dispaly that number

i.e:

here I want lblcode display--> 11 (becuse number 11 isn't in table)

how I can do it?

best regards

neda

Indresh

0 1

Reply
ANSWER
User: Indresh
Replied: on May 04, 2017 09:50 AM
Report

Hi nedash,

Please refer the below sql query.You need to create same sql query as per your table structure and requirement.

SQL

DECLARE  @TempTable AS TABLE(Id INT,Code INT)
INSERT INTO @TempTable VALUES(1, 10);
INSERT INTO @TempTable VALUES(2, 11);
INSERT INTO @TempTable VALUES(3, 12);
INSERT INTO @TempTable VALUES(4, 13);
INSERT INTO @TempTable VALUES(5, 14);

DECLARE @MinCode INT 
SET @MinCode = (SELECT MIN(Code) FROM @TempTable) -- Set Minimum code value
DECLARE @MaxCode INT 
SET @MaxCode = (SELECT MAX(Code) FROM @TempTable) -- Set Maximum code value
DECLARE  @LatestCode INT
WHILE @MinCode <= @MaxCode
BEGIN
    IF NOT EXISTS(SELECT Code FROM @TempTable WHERE Code = @MinCode)  
		BEGIN	
			SET @LatestCode = @MinCode 
			BREAK;
		END
    ELSE
		BEGIN  	
			SET @LatestCode = (SELECT (MAX(Code) + 1) code FROM @TempTable)
		END
		
	SET @MinCode = @MinCode + 1	
END
SELECT @LatestCode as Code

Screenshot

nedash

0 2

Reply
User: nedash
Replied: on May 05, 2017 02:07 AM
Report

hi indresh

I wrote your SP and change code like below:

as 
begin
DECLARE @MinCode INT
SET @MinCode = (SELECT MIN(Code) FROM Estate_info) -- Set Minimum code value
DECLARE @MaxCode INT
SET @MaxCode = (SELECT MAX(Code) FROM Estate_info) -- Set Maximum code value
DECLARE  @LatestCode INT
WHILE @MinCode <= @MaxCode
BEGIN
    IF NOT EXISTS(SELECT Code FROM Estate_info WHERE Code = @MinCode) 
        BEGIN  
            SET @LatestCode = @MinCode
            BREAK;
        END
    ELSE
        BEGIN  
            SET @LatestCode = (SELECT (MAX(Code) + 1) code FROM Estate_info)
        END
         
    SET @MinCode = @MinCode + 1
END
SELECT @LatestCode as Code

END

but when I run this SP it return Null:

this is Estate_info table:

and excute SP:

Indresh

0 3

Reply
User: Indresh
Replied: on May 05, 2017 02:48 AM
Modified: on May 05, 2017 02:53 AM
Report

Hi nedash,

You need to cross check your code column datatype.If your code column dataType NVARCHAR or VARCHAR So please chnage your @MinCode datatype @MaxCode datatype as per your code column datatype.

Example:

If your code column datatype NVARCHAR so please chnage your datatype like below.

DECLARE @MinCode NVARCHAR(10) 
DECLARE @MaxCode NVARCHAR(10)

Select missing number from a column using while loop in SQL Server

Company

Explore

Follow us

Contact Us