Membuat nomor Urut SQL Server

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE
DBMedical
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.buatfamilyfolder
    -- Add the parameters for the stored procedure here
    @id_familyfolder nchar(10),
    @nama_kk varchar(50),
    @alamat_kk varchar(100),
    @id_provinsi nchar(2),
    @id_kabupaten nchar(4),
    @id_kecamatan nchar(7),
    @id_desa nchar(10),
    @jumlahbaris int,
    @urutanfamilyfolder int,
    @jumlahdigit int
AS
BEGIN
SET NOCOUNT ON;
SET @jumlahbaris = (SELECT COUNT(*) FROM tb_Mst_FamilyFolder )
IF @jumlahbaris =0
     SET @id_familyfolder = '0000000001'
ELSE
   BEGIN
   SET @urutanfamilyfolder  = CONVERT (Int,(SELECT TOP 1 id_familyfolder FROM tb_Mst_FamilyFolder ORDER BY id_FamilyFolder DESC))+1
   SET @jumlahdigit =  LEN(CONVERT(varchar,@urutanfamilyfolder))
       IF @jumlahdigit = 1
       SET @id_familyfolder = '000000000'+ CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =2
       SET @id_familyfolder = '00000000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =3
       SET @id_familyfolder = '0000000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =4
       SET @id_familyfolder = '000000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =5
       SET @id_familyfolder = '00000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =6
       SET @id_familyfolder = '0000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =7
       SET @id_familyfolder = '000'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =8
       SET @id_familyfolder = '00'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE IF @jumlahdigit =9
       SET @id_familyfolder = '0'+CONVERT(varchar,@urutanfamilyfolder)
       ELSE
       SET @id_familyfolder = CONVERT(varchar,@urutanfamilyfolder)
  END
  INSERT INTO dbo.tb_Mst_FamilyFolder
    (id_FamilyFolder, Nama_KK, Alamat_KK, id_Provinsi, id_kabupaten, id_kecamatan, id_Desa )
   VALUES
    (@id_familyfolder, @nama_kk, @alamat_kk, @id_provinsi, @id_kabupaten, @id_kecamatan, @id_desa )
END
GO