摘要:本文主要向大家介绍了SQLServer数据库开发接口生成方法,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了SQLServer数据库开发接口生成方法,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
为提高开发效率,生成固定格式的接口是必须的,以下以提供新增/修改/删除/读取接口为例:
以常见的表结构为例,特殊表结构可自己尝试去调整方法
1、在Test数据库生成方法
[sql] view plain copy
1. if OBJECT_ID('Curdsqlstring','P') is not null
2. DROP PROC Curdsqlstring
3. go
4. CREATE PROCEDURE Curdsqlstring(
5. @TableName sysname
6. )
7. with encryption
8. AS
9. if OBJECT_ID(@TableName,'U') is null
10. return 0
11.
12. DECLARE @S NVARCHAR(MAX)
13. ,@ColName NVARCHAR(MAX)
14. ,@Identity sysname
15. ,@IdentityWHERE NVARCHAR(100)
16. ,@Insert0 NVARCHAR(MAX)
17. ,@Insert1 NVARCHAR(MAX)
18. ,@Insert2 NVARCHAR(MAX)
19. ,@Insert3 NVARCHAR(MAX)
20. ,@UPDATE0 NVARCHAR(MAX)
21. ,@UPDATE1 NVARCHAR(MAX)
22. ,@UPDATE2 NVARCHAR(MAX)
23. ,@UPDATE3 NVARCHAR(MAX)
24. ,@SELECTWHERE0 NVARCHAR(MAX)
25. ,@SELECTWHERE1 NVARCHAR(MAX)
26. ,@SELECT0 NVARCHAR(MAX)
27. ,@SELECT1 NVARCHAR(MAX)
28. ,@DELETE NVARCHAR(MAX)
29. ,@ColName2 NVARCHAR(MAX)
30. ,@ObjectID INT
31. ,@UPDATECol1 NVARCHAR(MAX)
32. ,@ColAll NVARCHAR(MAX)
33. ,@InputCol sysname
34. ,@Write NVARCHAR(100)
35. ,@OutputAll NVARCHAR(1000)
36. ,@TableName2 sysname
37. SELECT @Write='Roy'+char(32)+char(32)+char(32)+convert(varchar(10),getdate(),120),@Insert2='',@Insert3=''
38.
39. SELECT
40. @S=CASE WHEN a.is_computed=1 THEN @S ELSE ISNULL(@s+',','')+'@'+Name+char(32)+ TYPE_NAME(user_type_id)+CASE when user_type_id in(34,35,36,48,52,56,58,59,60,61,62,98,99,104,122,127,189,241,256,241,40,41,129) then ''
41. when user_type_id in(106,108) then '('+rtrim(Precision)+','+rtrim(Scale)+')'
42. when user_type_id in (231,239) then CASE when max_length=-1 then '(max)' else '('+rtrim(max_length/2)+')' end
43. when max_length=-1 then '(Max)'
44. else '('+rtrim(max_length)+')' end
45. +CASE when is_identity=1 then char(32)+'OUTPUT'
46. else '' end END,
47. @ColName=CASE when is_identity=1 OR a.is_computed=1 THEN @ColName else isnull(@ColName+',','')+quotename(Name) end,
48. @ColName2=CASE when is_identity=1 OR a.is_computed=1 then @ColName2 else isnull(@ColName2+',','')+'@'+Name end,
49. @UPDATECol1=CASE when Name!=N'ID' AND NOT EXISTS ( SELECT 1
50. FROM sys.objects x
51. JOIN sys.indexes y ON x.type = N'PK'
52. AND x.name = y.name
53. JOIN sysindexkeys z ON z.id = x.object_id
54. AND z.indid = y.index_id
55. AND z.colid = a.Column_id
56. AND x.object_id=a.object_id) AND a.is_computed=0 THEN ISNULL(@UPDATECol1+',','')+QUOTENAME(Name)+'='+'@'+Name ELSE @UPDATECol1 END,
57. @IdentityWHERE=isnull(@IdentityWHERE,'')+CASE when COLUMNPROPERTY (OBJECT_ID, Name , 'IsIdentity' )=1 OR Name=N'ID' then quotename(Name)+'='+'@'+Name ELSE '' end,
58. @ColAll=isnull(@ColAll+',','')+quotename(Name),
59. @OutputAll=isnull(@OutputAll+',','')+'@'+Name
60. from
61. Sys.columns AS a
62. WHERE
63. a.OBJECT_ID=OBJECT_ID(@TableName)
64. order by CASE when Name='ID' then 0 else 1 end,Column_id
65.
66. IF @IdentityWHERE = ''
67. SELECT @IdentityWHERE = @IdentityWHERE+CASE WHEN @IdentityWHERE > '' AND EXISTS ( SELECT 1
68. FROM sys.objects x
69. JOIN sys.indexes y ON x.type = N'PK'
70. AND x.name = y.name
71. JOIN sysindexkeys z ON z.id = a.object_id
72. AND z.indid = y.index_id
73. AND z.colid = a.Column_id ) THEN ' AND '
74. ELSE ''
75. END
76. + CASE WHEN EXISTS ( SELECT 1
77. FROM sys.objects x
78. JOIN sys.indexes y ON x.type = N'PK'
79. AND x.name = y.name
80. JOIN sysindexkeys z ON z.id = a.object_id
81. AND z.indid = y.index_id
82. AND z.colid = a.Column_id )
83. THEN QUOTENAME(name) + '=' + '@' + name
84. ELSE ''
85. END
86. FROM sys.columns AS a
87. WHERE object_id = OBJECT_ID(@TableName)
88. ORDER BY column_id
89.
90. IF @IdentityWHERE=''
91. SELECT TOP 1 @IdentityWHERE=quotename(Name)+'='+'@'+Name FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(@TableName) ORDER BY column_id
92. --主键为第一列
93. SELECT @Identity=replace(left(@s,charindex(',',@s)-1),char(32)+'output','')
94. ,@ObjectID=OBJECT_ID(@TableName)
95. ,@TableName=replace(replace(stuff(@TableName,1,charindex('.',@TableName),''),']',''),'[','')
96. ,@TableName2=CASE WHEN @TableName NOT LIKE '%.%' THEN ''+OBJECT_SCHEMA_NAME(@ObjectID)+'.'+@TableName ELSE @TableName END
97.
98. SELECT @Insert1='CREATE PROCEDURE c'+@TableName+char(10)+char(9)+char(9)+'('+@s+')'+char(10)+'AS',
99. @Insert2='INSERT INTO '+@TableName2+char(10)+char(9)+'('+@ColName+')',
100. @Insert3='VALUES'+char(10)+char(9)+'('+@ColName2+')'
101. +CASE when exists(SELECT 1 from Sys.columns WHERE OBJECT_ID=@ObjectID and is_identity=1)
102. then char(10)+char(10)+'SET '+left(@Identity,charindex(char(32),@Identity)-1)+'=SCOPE_IDENTITY()'
103. else '' end,
104. @UPDATE1='CREATE PROCEDURE u'+@TableName+char(10)+char(9)+char(9)+'('+replace(@s,char(32)+'output','')+')'+char(10)+'AS',
105. @UPDATE2='UPDATE '+@TableName2+char(10)+'SET '+@UPDATECol1,
106. @UPDATE3='WHERE '+@IdentityWHERE,
107. @SELECTWHERE1='CREATE PROCEDURE r'+@TableName+'By'+stuff(left(@Identity,charindex(char(32),@Identity)-1),1,1,'')+char(10)+char(9)+char(9)+'('+@Identity+')'+char(10)+'AS'+char(10)
108. +'SET NOCOUNT ON;'+char(10)+'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'+char(10)+
109. 'SELECT '+char(10)+char(9)+@ColAll+char(10)+'From'+char(10)+char(9)+@TableName2+char(10)+'WHERE'+char(10)+char(9)+@IdentityWHERE,
110. @SELECT1='CREATE PROCEDURE r'+@TableName+'ForAll'+char(10)+'AS'+char(10)+
111. 'SET NOCOUNT ON;'+char(10)+'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'+char(10)+
112. 'SELECT '+char(10)+char(9)+@ColAll+char(10)+'From'+char(10)+char(9)+@TableName2,
113. @DELETE='CREATE PROCEDURE d'+@TableName+char(10)+char(9)+'('+@Identity+')'+char(10)+'AS'+char(10)+'DELETE '+@TableName2+char(32)+'WHERE'+char(32)+@IdentityWHERE,
114. @InputCol=CASE when exists(SELECT 1 from Sys.columns WHERE OBJECT_ID=@ObjectID and is_identity=1) then @Identity else '' end
115.
116.
117.
118. --Insert
119. SELECT @Insert0=
120. 'if OBJECT_ID('+quotename('c'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('c'+@TableName)
121. +char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+
122. '%%存储过程名:c'+@TableName+char(10)+char(10)+
123. '%%输入参数:'+@ColName2+char(10)+char(10)+
124. '%%输出参数:'+@InputCol+char(10)+char(10)+
125. '%%功能:新增记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+
126. '%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/'
127. ,
128.
129. --UPDATE
130.
131. @UPDATE0=
132. 'if OBJECT_ID('+quotename('u'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('u'+@TableName)
133. +char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+
134. '%%存储过程名:u'+@TableName+char(10)+char(10)+
135. '%%输入参数:'+@OutputAll+char(10)+char(10)+
136. '%%输出参数:'+char(10)+char(10)+
137. '%%功能:修改记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+
138. '%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/'
139. ,
140. --SELECTWHERE
141. @SELECTWHERE0=
142. 'if OBJECT_ID('+quotename('r'+@TableName+'By'+stuff(left(@Identity,charindex(char(32),@Identity)-1),1,1,''),'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('r'+@TableName+'By'+stuff(left(@Identity,charindex(char
143.
144. (32),@Identity)-1),1,1,''))
145. +char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)
146. +'%%存储过程名:r'+@TableName+'By'+stuff(left(@Identity,charindex(char(32),@Identity)-1),1,1,'')+char(10)+char(10)
147. +N'%%输入参数:'+@Identity+char(10)+char(10)
148. +N'%%输出参数:'+char(10)+char(10)
149. +N'%%功能:根据条件读取记录在表'+@TableName+char(10)+replicate('*',160)+char(10)
150. +'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',
151. --SELECT
152. @SELECT0=
153. 'if OBJECT_ID('+quotename('r'+@TableName+'ForAll','''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('r'+@TableName+'ForAll')
154. +char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)
155. +'%%存储过程名:r'+@TableName+'ForAll'+char(10)+char(10)
156. +'%%输入参数:'+char(10)+char(10)
157. +'%%输出参数:'+char(10)+char(10)
158. +'%%功能:读取记录在表'+@TableName+char(10)+replicate('*',160)+char(10)
159. +'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',
160.
161. @DELETE=
162. 'if OBJECT_ID('+quotename('d'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('d'+@TableName)
163. +char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)
164. +'%%存储过程名:d'+@TableName+char(10)+char(10)
165. +'%%输入参数:'+char(10)+char(10)
166. +'%%输出参数:'+@Identity+char(10)+char(10)
167. +'%%功能:删除记录在表'+@TableName+char(10)+replicate('*',160)+char(10)
168. +'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/'+char(10)
169. +@DELETE+char(13)+char(10)+'Go'+char(13)+char(10)
170.
171. print 'USE '+db_name()+char(13)+char(10)+'Go'+char(13)+char(10)
172. PRINT @Insert0
173. print @Insert1
174. print @Insert2
175. print @Insert3
176. PRINT 'Go'+char(13)+char(10)
177. print @UPDATE0
178. print @UPDATE1
179. print @UPDATE2
180. print @UPDATE3
181. PRINT 'Go'+char(13)+char(10)
182. print @SELECTWHERE0
183. print @SELECTWHERE1
184. PRINT 'Go'+char(13)+char(10)
185. print @SELECT0
186. print @SELECT1
187. PRINT 'Go'+char(13)+char(10)
188. print @DELETE
189.
190.
191. go
192. exec sp_ms_marksystemobject 'CurdSqlString'--添加在系统存储过程目录
193. go
2、创建表Tab1
e.g.
USE [test]GO
/****** Object: Table [dbo].[Tab1] Script Date: 2016/5/6 11:51:47 ******/IF OBJECT_ID('Tab1','U') IS NOT NULLDROP TABLE [dbo].[Tab1]GOCREATE TABLE [dbo].[Tab1](
[ID] [BIGINT] NULL,
[Name] [sysname] NOT NULL
) ON [PRIMARY]
GO
3、调用方法:
[sql] view plain copy
1. --调用方法:
2. exec CurdSqlString 'Tab1'-- 表名
显示效果:
[sql] view plain copy
1. USE test
2. Go
3. if OBJECT_ID('cTab1','P') is not null
4. DROP PROC [cTab1]
5. Go
6. /****************************************************************************************************************************************************************
7. %%存储过程名:cTab1
8.
9. %%输入参数:@ID,@Name
10.
11. %%输出参数:
12.
13. %%功能:新增记录在表Tab1
14. ****************************************************************************************************************************************************************
15. %%编写:Roy 2016-05-06
16.
17. ****************************************************************************************************************************************************************/
18. CREATE PROCEDURE cTab1
19. (@ID bigint,@Name sysname)
20. AS
21. INSERT INTO dbo.Tab1
22. ([ID],[Name])
23. VALUES
24. (@ID,@Name)
25. Go
26. if OBJECT_ID('uTab1','P') is not null
27. DROP PROC [uTab1]
28. Go
29. /****************************************************************************************************************************************************************
30. %%存储过程名:uTab1
31.
32. %%输入参数:@ID,@Name
33.
34. %%输出参数:
35.
36. %%功能:修改记录在表Tab1
37. ****************************************************************************************************************************************************************
38. %%编写:Roy 2016-05-06
39.
40. ****************************************************************************************************************************************************************/
41. CREATE PROCEDURE uTab1
42. (@ID bigint,@Name sysname)
43. AS
44. UPDATE dbo.Tab1
45. SET [Name]=@Name
46. WHERE [ID]=@ID
47. Go
48. if OBJECT_ID('rTab1ByID','P') is not null
49. DROP PROC [rTab1ByID]
50. Go
51. /****************************************************************************************************************************************************************
52. %%存储过程名:rTab1ByID
53.
54. %%输入参数:@ID bigint
55.
56. %%输出参数:
57.
58. %%功能:根据条件读取记录在表Tab1
59. ****************************************************************************************************************************************************************
60. %%编写:Roy 2016-05-06
61.
62. ****************************************************************************************************************************************************************/
63. CREATE PROCEDURE rTab1ByID
64. (@ID bigint)
65. AS
66. SET NOCOUNT ON;
67. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
68. SELECT
69. [ID],[Name]
70. From
71. dbo.Tab1
72. WHERE
73. [ID]=@ID
74. Go
75. if OBJECT_ID('rTab1ForAll','P') is not null
76. DROP PROC [rTab1ForAll]
77. Go
78. /****************************************************************************************************************************************************************
79. %%存储过程名:rTab1ForAll
80.
81. %%输入参数:
82.
83. %%输出参数:
84.
85. %%功能:读取记录在表Tab1
86. ****************************************************************************************************************************************************************
87. %%编写:Roy 2016-05-06
88.
89. ****************************************************************************************************************************************************************/
90. CREATE PROCEDURE rTab1ForAll
91. AS
92. SET NOCOUNT ON;
93. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
94. SELECT
95. [ID],[Name]
96. From
97. dbo.Tab1
98. Go
99. if OBJECT_ID('dTab1','P') is not null
100. DROP PROC [dTab1]
101. Go
102. /****************************************************************************************************************************************************************
103. %%存储过程名:dTab1
104.
105. %%输入参数:
106.
107. %%输出参数:@ID bigint
108.
109. %%功能:删除记录在表Tab1
110. ****************************************************************************************************************************************************************
111. %%编写:Roy 2016-05-06
112.
113. ****************************************************************************************************************************************************************/
114. CREATE PROCEDURE dTab1
115. (@ID bigint)
116. AS
117. DELETE dbo.Tab1 WHERE [ID]=@ID
118. Go
本文由职坐标整理并发布,了解更多内容,请关注职坐标SQL Server数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号