March 26, 2011

generate ID from any Table through stored procedure

You can write following code to generate ID from any Table through stored procedure. Here Generate_Id is parameter to pass Id field name. You may have different field name for your table so this stored procedure is common for all tables. This stored procedure may be very helpful for you.
This is a Common code for all table just call this stored procedure and pass table Name, Criteria and Primary key column Name as parameter.

 set ANSI_NULLS OFF  
 set QUOTED_IDENTIFIER OFF  
 GO  
 Create PROCEDURE [dbo].[Generate_ID]   
 @TablePK CHAR(50),   
 @Table CHAR(30),   
 @Criteria VARCHAR(300)=''   
 AS   
 BEGIN   
 IF @Criteria <> ''   
 BEGIN   
 EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+' WHERE '+@Criteria+'')   
 END   
 ELSE   
 BEGIN   
 EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+'')   
 END   
 END   
 RETURN