Powered by Passion

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' ...

Hi, if you have a table which contain a Id field. Such like "10001" If you want to Custom the ID like "EM00001" you can easily manage it using store procedure. Consider the store Procedure: USE [HRMDB] GO /****** Object: StoredProcedure [dbo].[NEW_EMP_CODE_CREATION] Script Date: 01/10/2012 19:09:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[NEW_EMP_CODE_CREATION] AS BEGIN SET NOCOUNT ON DECLARE @empID_ID VARCHAR(20), @countRow INT SELECT @countRow=COUNT(*) FROM EMPLOYEE_INFORMATION IF(@countRow<>0) BEGIN DECLARE @num int SELECT @num=(max(CONVERT(int, SUBSTRING( EMP_CODE,3,8)))+1) FROM EMPLOYEE_INFORMATION SET @empID_ID=(SELECT Distinct('EM'+RIGHT ('000000'+ CAST(@num as varchar), 6)) FROM EMPLOYEE_INFOR...