CRUD 定义了用于处理数据的基本原子操作。
它代表创建(Create)、读取(Read)更新(Update)和删除(Delete)操作。
下面的脚本可以实现简单的curd功能。
编写存储过程主要考虑问题:
- 第一个字段不一定是主键
- 主键不一定只对应一个字段。
- 有字段具有Identity属性的时候,需要返回最后的标准值。
- 生成的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
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
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