Tuesday, January 10, 2012

Auto Custome ID Generation from SQL Table

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_INFORMATION)  
           END  
      ELSE  
           SET     @empID_ID='EM000001'  
      SELECT @empID_ID AS EMP_CODE       
 END  

SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT...