SQL auto generate last column id without auto increment

If we want to get a table autogenerate column id, without auto increment column. we can do it easily.
we can also made it globally for all table. We can create a table name paramaterize store procedure that
will give us table last column ID without using auto increment column.

 CREATE PROCEDURE [dbo].[spSET_GetDB_TablePKID]  
      @tableName [varchar](100),  
      @locationId [int]  
 AS  
 BEGIN  
 declare @newID varchar(20)  
 declare @dbID varchar(20)  
 declare @generateID bigint  
 declare @ReturnValue varchar(100)  
  if exists(select * from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
   begin  
   --table name exists  
   set @ReturnValue= (select MaxID+1 from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
   end  
  else --table name is not exists.  
   begin  
   -- generate new first ID for the supplied table  
   select @dbID= DBid from DB where DBLocationID=@locationId  
   set @newID=@dbID+'00000000000001'  
   set @generateID=convert(bigint,@newID)  
   set @ReturnValue=@generateID  
   end   
   select @ReturnValue  
 end  

declar the store procedure in C#

 public long GetTablekeyID(string tableName, int locationId)  
     {  
       long id = 0;  
       // id = _db.spSET_GetDB_TablePKID(tableName, locationId);  
       System.Data.Objects.ObjectResult<string> sid = this.DataContext.spSET_GetDB_TablePKID(tableName, locationId);  
       foreach (string aa in sid)  
       {  
         id = long.Parse(aa.ToString());  
       }  
       return id;  
     }  

Now call from anywhere with your any table name. it will return last id of your table. here locationID is optional.

Popular posts from this blog

mvc razor textboxfor change event change another textboxfor value

WPF Crystal Report Viewer Using SAP

ASP.NET MVC razor SAP Crystal report