生成CURD脚本的存储过程

 CRUD 定义了用于处理数据的基本原子操作。

　　它代表创建（Create）、读取（Read）更新（Update）和删除（Delete）操作。

 

 下面的脚本可以实现简单的curd功能。

编写存储过程主要考虑问题：

1. 第一个字段不一定是主键
2. 主键不一定只对应一个字段。
3. 有字段具有Identity属性的时候，需要返回最后的标准值。
4. 生成的Curd 代码格式要整齐好看。

Use  test
Go
If   object_id ( ' curdsql ' )  Is   Not   Null
     Drop   Proc  curdsql
Go
/* curdsql(V1.0) ok_008 2009-4-6 */
Create   Proc  curdsql
(
     @object  sysname
)
As
Set  Nocount  On
If   object_id ( @object , ' U ' )  Is    Null
Begin
     Raiserror   50001  N ' 无效的表名. '
     Return ( 1 )
End

Set   @object = object_name ( object_id ( @object ))
If   object_id ( ' tempdb..#columns ' )  Is   Not   Null
     Drop   Table  #columns

Select     column_id  As  seq,
        name,
        Type_name(user_type_id)  +
         Case
             When  user_type_id  In ( 231 , 239 )  Then  
                N '  ( ' + Case  max_length 
                         When   - 1   Then  N ' Max '  
                         Else   Rtrim (max_length / 2 ) 
                      End +
                N ' ) '
             When  user_type_id  In ( 106 , 108 )  Then  N '  ( ' + Rtrim ( precision ) + N ' , ' + Rtrim (scale) + N ' ) '
             When  user_type_id  In ( 62 , 165 , 167 , 173 , 175 )  Then  N '  ( ' + Rtrim (max_length) + N ' ) '
             Else   ''
         End   As  type,
        is_identity,
         Case
             When   Exists ( Select   1
                             From  sys.indexes  As  x
                                 Inner   Join  sys.index_columns  As  y  On  y. object_id = a. object_id
                                         And  y.index_id = x.index_id
                             Where  x.is_primary_key = 1
                                     And  x. object_id = a. object_id
                                     And  y.column_id = a.column_id
                        )  Then   1
             Else   0
         End   As  is_primary_key
         Into  #columns
     From  sys.columns  As  a
     Where   object_id = object_id ( @object )

Declare   @sql   nvarchar ( 4000 ),
         @ProcName  sysname,
         @Enter   nvarchar ( 2 ),
         @Tab   nvarchar ( 1 )

Select      @Enter = Char ( 13 ) + Char ( 10 ),
         @Tab = Char ( 9 )

Print  N ' Use  ' + Quotename ( db_name ()) + @Enter + N ' Go '

Set   @ProcName = N ' c ' + @object
Set   @sql = N ' If object_id( ''' + @ProcName + N ''' ) Is Not Null  ' + @Enter + @Tab + N ' Drop Proc  ' + @ProcName + @Enter + ' Go ' + @Enter +
            N ' Create Proc  ' + @ProcName + @Enter + N ' ( ' + @Enter + @Tab + Replace ( Stuff (( Select  N ' @,@ ' + name + N '   ' + type + Case  is_identity  When   1   Then  N '  Output '   Else   ''   End   From  #Columns  Order   By  seq  For  Xml Path( '' )), 1 , 2 , '' ),N ' @, ' ,N ' , ' + @Enter + @Tab ) + @Enter + N ' ) ' + @Enter + N ' As ' + @Enter +
            N ' Insert Into  ' + Quotename ( @object ) + N ' ( ' + Stuff (( Select  N ' , ' + Quotename (name)  From  #Columns  Where  is_identity = 0   Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ) + N ' ) ' + @Enter + @Tab +
            N ' Select  ' + Stuff (( Select  N ' ,@ ' + name  From  #Columns  Where  is_identity = 0   Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ) + @Enter +
             Isnull (( Select  N ' Set @ ' + name + N ' =Scope_identity() '   From  #Columns  Where  is_identity = 1 ), '' ) + @Enter + N ' Go '
Print   @sql

Set   @ProcName = N ' u ' + @object
Set   @sql = N ' If object_id( ''' + @ProcName + N ''' ) Is Not Null  ' + @Enter + @Tab + N ' Drop Proc  ' + @ProcName + @Enter + ' Go ' + @Enter +
            N ' Create Proc  ' + @ProcName + @Enter + N ' ( ' + @Enter + @Tab + Replace ( Stuff (( Select  N ' @,@ ' + name + N '   ' + type  From  #Columns  Order   By  seq  For  Xml Path( '' )), 1 , 2 , '' ),N ' @, ' ,N ' , ' + @Enter + @Tab ) + @Enter + N ' ) ' + @Enter + N ' As ' + @Enter +
            N ' Update  ' + Quotename ( @object ) + @Enter + @Tab +
            N ' Set ' + @Tab + Replace ( Stuff (( Select  N ' @, ' + Quotename (name) + N ' =@ ' + name  From  #Columns  Where  is_primary_key = 0   And  is_identity = 0   Order   By  seq  For  Xml Path( '' )), 1 , 2 , '' ),N ' @, ' ,N ' , ' + @Enter + @Tab + @Tab ) + @Enter + @Tab +
            N ' Where  ' + Stuff (( Select  N '  And  ' + Quotename (name) + N ' =@ ' + name  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 5 , '' ) + @Enter ++ N ' Go '
Print   @sql

Set   @ProcName = N ' r ' + @object + N ' ForAll '
Set   @sql = N ' If object_id( ''' + @ProcName + N ''' ) Is Not Null  ' + @Enter + @Tab + N ' Drop Proc  ' + @ProcName + @Enter + ' Go ' + @Enter +
            N ' Create Proc  ' + @ProcName + @Enter + N ' As ' + @Enter +
            N ' Select ' + @Tab + Replace ( Stuff (( Select  N ' , ' + Quotename (name)  From  #Columns  Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ),N ' , ' ,N ' , ' + @Enter + @Tab + @Tab ) + @Enter + @Tab +
            N ' From  ' + Quotename ( @object ) + @Enter + N ' Go '
Print   @sql
        
Set   @ProcName = N ' r ' + @object + N ' By ' + Stuff (( Select  N ' And ' + name  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 3 , '' )
Set   @sql = N ' If object_id( ''' + @ProcName + N ''' ) Is Not Null  ' + @Enter + @Tab + N ' Drop Proc  ' + @ProcName + @Enter + ' Go ' + @Enter +
            N ' Create Proc  ' + @ProcName + @Enter + N ' ( ' + @Enter + @Tab + Replace ( Stuff (( Select  N ' @,@ ' + name + N '   ' + type  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 2 , '' ),N ' @, ' ,N ' , ' + @Enter + @Tab ) + @Enter + N ' ) ' + @Enter + N ' As ' + @Enter +
            N ' Select ' + @Tab + Replace ( Stuff (( Select  N ' , ' + Quotename (name)  From  #Columns  Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ),N ' , ' ,N ' , ' + @Enter + @Tab + @Tab ) + @Enter + @Tab +
            N ' From  ' + Quotename ( @object ) + @Enter + @Tab +
            N ' Where  ' + Replace ( Stuff (( Select  N ' , ' + Quotename (name) + N ' =@ ' + name  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ),N ' , ' , @Enter + @Tab + @Tab + @Tab + N ' And  ' ) + @Enter + N ' Go '
Print   @sql

Set   @ProcName = N ' d ' + @object
Set   @sql = N ' If object_id( ''' + @ProcName + N ''' ) Is Not Null  ' + @Enter + @Tab + N ' Drop Proc  ' + @ProcName + @Enter + ' Go ' + @Enter +
            N ' Create Proc  ' + @ProcName + @Enter + N ' ( ' + @Enter + @Tab + Replace ( Stuff (( Select  N ' @,@ ' + name + N '   ' + type  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 2 , '' ),N ' @, ' ,N ' , ' + @Enter + @Tab ) + @Enter + N ' ) ' + @Enter + N ' As ' + @Enter +
            N ' Delete  ' + @Enter + @Tab +
            N ' From  ' + Quotename ( @object ) + @Enter + @Tab +
            N ' Where  ' + Replace ( Stuff (( Select  N ' , ' + Quotename (name) + N ' =@ ' + name  From  #Columns  Where  is_primary_key = 1   Order   By  seq  For  Xml Path( '' )), 1 , 1 , '' ),N ' , ' , @Enter + @Tab + @Tab + @Tab + N ' And  ' ) + @Enter + N ' Go '
Print   @sql

Drop   Table  #columns

Go

 

调用存储过程curdsql:

Exec  curdsql Batch

 

执行结果：

 

Use   [ test ]
Go
If   object_id ( ' cBatch ' )  Is   Not   Null  
     Drop   Proc  cBatch
Go
Create   Proc  cBatch
(
     @BatchID   uniqueidentifier ,
     @AddedOn   datetime ,
     @Action   varchar  ( 32 ),
     @Item   nvarchar  ( 425 ),
     @Parent   nvarchar  ( 425 ),
     @Param   nvarchar  ( 425 ),
     @BoolParam   bit ,
     @Content   image ,
     @Properties   ntext
)
As
Insert   Into   [ Batch ] ( [ BatchID ] , [ AddedOn ] , [ Action ] , [ Item ] , [ Parent ] , [ Param ] , [ BoolParam ] , [ Content ] , [ Properties ] )
     Select   @BatchID , @AddedOn , @Action , @Item , @Parent , @Param , @BoolParam , @Content , @Properties

Go
If   object_id ( ' uBatch ' )  Is   Not   Null  
     Drop   Proc  uBatch
Go
Create   Proc  uBatch
(
     @BatchID   uniqueidentifier ,
     @AddedOn   datetime ,
     @Action   varchar  ( 32 ),
     @Item   nvarchar  ( 425 ),
     @Parent   nvarchar  ( 425 ),
     @Param   nvarchar  ( 425 ),
     @BoolParam   bit ,
     @Content   image ,
     @Properties   ntext
)
As
Update   [ Batch ]
     Set      [ AddedOn ] = @AddedOn ,
         [ Action ] = @Action ,
         [ Item ] = @Item ,
         [ Parent ] = @Parent ,
         [ Param ] = @Param ,
         [ BoolParam ] = @BoolParam ,
         [ Content ] = @Content ,
         [ Properties ] = @Properties
     Where   [ BatchID ] = @BatchID
Go
If   object_id ( ' rBatchForAll ' )  Is   Not   Null  
     Drop   Proc  rBatchForAll
Go
Create   Proc  rBatchForAll
As
Select      [ BatchID ] ,
         [ AddedOn ] ,
         [ Action ] ,
         [ Item ] ,
         [ Parent ] ,
         [ Param ] ,
         [ BoolParam ] ,
         [ Content ] ,
         [ Properties ]
     From   [ Batch ]
Go
If   object_id ( ' rBatchByBatchID ' )  Is   Not   Null  
     Drop   Proc  rBatchByBatchID
Go
Create   Proc  rBatchByBatchID
(
     @BatchID   uniqueidentifier
)
As
Select      [ BatchID ] ,
         [ AddedOn ] ,
         [ Action ] ,
         [ Item ] ,
         [ Parent ] ,
         [ Param ] ,
         [ BoolParam ] ,
         [ Content ] ,
         [ Properties ]
     From   [ Batch ]
     Where   [ BatchID ] = @BatchID
Go
If   object_id ( ' dBatch ' )  Is   Not   Null  
     Drop   Proc  dBatch
Go
Create   Proc  dBatch
(
     @BatchID   uniqueidentifier
)
As
Delete  
     From   [ Batch ]
     Where   [ BatchID ] = @BatchID
Go