生成CURD脚本的存储过程

现在的位置: 首页 > 综合 > 正文

RSS

生成CURD脚本的存储过程

2013年10月22日 ⁄ 综合 ⁄ 共 5931字 ⁄ 字号小中大 ⁄ 评论关闭

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

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

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

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

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

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/

-->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