生成CURD脚本的存储过程
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
