Dynamic Store Procedure

Integer Auto Increment

CREATE PROCEDURE dbo.SP_INTAUTODETAIL
AS
BEGIN
select MAX(NID)+1 from
(
select isnull(MAX(<filed>),0) NID from <tblname>
) N
END

String Auto Increment

CREATE PROCEDURE dbo.SP_STRAUTODETAIL

AS
BEGIN
--@NewsID is Filed Name
declare @id int

select @id =CONVERT(varchar,count(*)+1) from  <tblName>

if (@id <=9)
begin
select @NewsID='News00'+CONVERT(varchar,@id)
end
else if (@id <=99)
begin
select @NewsID='News0'+CONVERT(varchar,@id)
end
else
begin
select @NewsID='News'+CONVERT(varchar,@id)
end



END

Insert SP

 ALTER PROCEDURE [dbo].[SP_INSERT]
 @TABLENAME VarChar(100)=Null,
 @FIELDS VarChar(8000)=Null,
 @VALUES VarChar(8000)=Null
 AS
 BEGIN
   DECLARE @QUERY VARCHAR(8000)
   Set @Query = 'INSERT INTO '+ @TABLENAME + ' '
   Set @Query = @Query + '( ' + @FIELDS + ')' + ' VALUES '
    Set @Query = @Query + '( ' + @VALUES + ')'
   EXEC(@Query)

 END

Update SP

ALTER PROCEDURE [dbo].[sp_Update] 

@TableName VarChar(100) = '',

@Fields VarChar(8000) = '*',

@Criteria VarChar(8000) = Null 

AS 

 Begin 

  Declare @Stmt Varchar(8000) 

  Set @Stmt = '

Update ' + @TableName + ' 

Set ' + @Fields + ' 

Where 1 = 1 ' + @Criteria 

  Exec(@Stmt)

 End

Delete SP

ALTER PROCEDURE [dbo].[SP_DELETE] 

 @TABLENAME VarChar(100)=Null,

 @CRITERIA VarChar(8000)=Null 

 AS 

 BEGIN 

   DECLARE @QUERY VARCHAR(8000) 

   Set @Query = ' Delete From ' + @TABLENAME 

   Set @Query = @Query + ' WHERE 1 = 1 ' 

   Set @Query = @Query + @CRITERIA 

   EXEC(@Query)

 END