你的位置:首页 > 数据库

[数据库]SQL Server开发接口生成方法


为提高开发效率,生成固定格式的接口是必须的,以下以提供新增/修改/删除/读取接口为例:

以常见的表结构为例,特殊表结构可自己尝试去调整方法

 

列名

数据类型

说明

object_id

int

此列所属对象的 ID。

name

sysname

列名。在对象中是唯一的。

column_id

int

列的 ID。在对象中是唯一的。

列 ID 可以不按顺序排列。

system_type_id

tinyint

列的系统类型的 ID。

user_type_id

int

用户定义的列类型的 ID。

若要返回该类型的名称,请在该列中联接到 sys.types 目录视图。

max_length

smallint

列的最大长度(字节)。

-1 = 列数据类型为 varchar(max)nvarchar(max)varbinary(max)

对于 text 列,max_length 值将是 16,或者是 sp_tableoption 'text in row' 所设置的值。

precision

tinyint

如果列包含的是数值,则为该列的精度;否则为 0。

scale

tinyint

如果基于数值,则为列的小数位数;否则为 0。

collation_name

sysname

如果列包含的是字符,则为该列排序规则的名称;否则为 NULL。

is_nullable

bit

1 = 列可为空。

is_ansi_padded

bit

1 = 如果列为字符、二进制或变量类型,则该列使用 ANSI_PADDING ON 行为。

0 = 列不是字符、二进制或变量类型。

is_rowguidcol

bit

1 = 列为声明的 ROWGUIDCOL。

is_identity

bit

1 = 列具有标识值

is_computed

bit

1 = 列为计算列。

is_filestream

bit

1 = 列为 FILESTREAM 列。

is_replicated

bit

1 = 列已复制。

is_non_sql_subscribed

bit

1 = 列具有非 SQL Server 订阅服务器。

is_merge_published

bit

1 = 列已合并发布。

is_dts_replicated

bit

1 = 使用 SSIS 复制列。

is_

bit

1 = 内容为完整的

0 = 内容是文档片段或列数据类型不是

int

如果列是 且已键入

0 = 没有

default_object_id

int

默认对象的 ID,无论它是独立对象 sys.sp_bindefault 还是内联列级 DEFAULT 约束。内联列级默认对象的 parent_object_id 列是对该表本身的反引用。

0 = 无默认值。

rule_object_id

int

使用 sys.sp_bindrule 绑定到列的独立规则的 ID。

0 = 无独立规则。有关列级 CHECK 约束的信息,请参阅sys.check_constraints (Transact-SQL)。

is_sparse

bit

1 = 列为稀疏列。有关详细信息,请参阅使用稀疏列。

is_column_set

bit

1 = 列为列集。有关详细信息,请参阅使用稀疏列

 

1、在Test数据库生成方法                                                                         


if OBJECT_ID('Curdsqlstring','P') is not null DROP PROC CurdsqlstringgoCREATE PROCEDURE Curdsqlstring( @TableName sysname )with encryptionASif OBJECT_ID(@TableName,'U') is null return 0 DECLARE @S NVARCHAR(MAX) ,@ColName NVARCHAR(MAX) ,@Identity sysname ,@IdentityWHERE NVARCHAR(100) ,@Insert0 NVARCHAR(MAX) ,@Insert1 NVARCHAR(MAX) ,@Insert2 NVARCHAR(MAX) ,@Insert3 NVARCHAR(MAX) ,@UPDATE0 NVARCHAR(MAX) ,@UPDATE1 NVARCHAR(MAX) ,@UPDATE2 NVARCHAR(MAX) ,@UPDATE3 NVARCHAR(MAX) ,@SELECTWHERE0 NVARCHAR(MAX) ,@SELECTWHERE1 NVARCHAR(MAX) ,@SELECT0 NVARCHAR(MAX) ,@SELECT1 NVARCHAR(MAX) ,@DELETE NVARCHAR(MAX) ,@ColName2 NVARCHAR(MAX) ,@ObjectID INT ,@UPDATECol1 NVARCHAR(MAX) ,@ColAll NVARCHAR(MAX) ,@InputCol sysname ,@Write NVARCHAR(100) ,@OutputAll NVARCHAR(1000) ,@TableName2 sysnameSELECT @Write='Roy'+char(32)+char(32)+char(32)+convert(varchar(10),getdate(),120),@Insert2='',@Insert3=''SELECT @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 '' when user_type_id in(106,108) then '('+rtrim(Precision)+','+rtrim(Scale)+')' when user_type_id in (231,239) then CASE when max_length=-1 then '(max)' else '('+rtrim(max_length/2)+')' end when max_length=-1 then '(Max)' else '('+rtrim(max_length)+')' end +CASE when is_identity=1 then char(32)+'OUTPUT' else '' end END, @ColName=CASE when is_identity=1 OR a.is_computed=1 THEN @ColName else isnull(@ColName+',','')+quotename(Name) end, @ColName2=CASE when is_identity=1 OR a.is_computed=1 then @ColName2 else isnull(@ColName2+',','')+'@'+Name end, @UPDATECol1=CASE when Name!=N'ID' AND NOT EXISTS ( SELECT 1 FROM sys.objects x JOIN sys.indexes y ON x.type = N'PK' AND x.name = y.name JOIN sysindexkeys z ON z.id = x.object_id AND z.indid = y.index_id AND z.colid = a.Column_id AND x.object_id=a.object_id) AND a.is_computed=0 THEN ISNULL(@UPDATECol1+',','')+QUOTENAME(Name)+'='+'@'+Name ELSE @UPDATECol1 END, @IdentityWHERE=isnull(@IdentityWHERE,'')+CASE when COLUMNPROPERTY (OBJECT_ID, Name , 'IsIdentity' )=1 OR Name=N'ID' then quotename(Name)+'='+'@'+Name ELSE '' end, @ColAll=isnull(@ColAll+',','')+quotename(Name), @OutputAll=isnull(@OutputAll+',','')+'@'+Namefrom Sys.columns AS aWHERE a.OBJECT_ID=OBJECT_ID(@TableName)order by CASE when Name='ID' then 0 else 1 end,Column_idIF @IdentityWHERE = '' SELECT @IdentityWHERE = @IdentityWHERE+CASE WHEN @IdentityWHERE > '' AND EXISTS ( SELECT 1 FROM sys.objects x JOIN sys.indexes y ON x.type = N'PK' AND x.name = y.name JOIN sysindexkeys z ON z.id = a.object_id AND z.indid = y.index_id AND z.colid = a.Column_id ) THEN ' AND ' ELSE '' END + CASE WHEN EXISTS ( SELECT 1 FROM sys.objects x JOIN sys.indexes y ON x.type = N'PK' AND x.name = y.name JOIN sysindexkeys z ON z.id = a.object_id AND z.indid = y.index_id AND z.colid = a.Column_id ) THEN QUOTENAME(name) + '=' + '@' + name ELSE '' END FROM sys.columns AS a WHERE object_id = OBJECT_ID(@TableName) ORDER BY column_idIF @IdentityWHERE='' SELECT TOP 1 @IdentityWHERE=quotename(Name)+'='+'@'+Name FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(@TableName) ORDER BY column_id--主键为第一列SELECT @Identity=replace(left(@s,charindex(',',@s)-1),char(32)+'output','') ,@ObjectID=OBJECT_ID(@TableName) ,@TableName=replace(replace(stuff(@TableName,1,charindex('.',@TableName),''),']',''),'[','') ,@TableName2=CASE WHEN @TableName NOT LIKE '%.%' THEN ''+OBJECT_SCHEMA_NAME(@ObjectID)+'.'+@TableName ELSE @TableName ENDSELECT @Insert1='CREATE PROCEDURE c'+@TableName+char(10)+char(9)+char(9)+'('+@s+')'+char(10)+'AS', @Insert2='INSERT INTO '+@TableName2+char(10)+char(9)+'('+@ColName+')', @Insert3='VALUES'+char(10)+char(9)+'('+@ColName2+')' +CASE when exists(SELECT 1 from Sys.columns WHERE OBJECT_ID=@ObjectID and is_identity=1) then char(10)+char(10)+'SET '+left(@Identity,charindex(char(32),@Identity)-1)+'=SCOPE_IDENTITY()' else '' end, @UPDATE1='CREATE PROCEDURE u'+@TableName+char(10)+char(9)+char(9)+'('+replace(@s,char(32)+'output','')+')'+char(10)+'AS', @UPDATE2='UPDATE '+@TableName2+char(10)+'SET '+@UPDATECol1, @UPDATE3='WHERE '+@IdentityWHERE, @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) +'SET NOCOUNT ON;'+char(10)+'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'+char(10)+ 'SELECT '+char(10)+char(9)+@ColAll+char(10)+'From'+char(10)+char(9)+@TableName2+char(10)+'WHERE'+char(10)+char(9)+@IdentityWHERE, @SELECT1='CREATE PROCEDURE r'+@TableName+'ForAll'+char(10)+'AS'+char(10)+ 'SET NOCOUNT ON;'+char(10)+'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'+char(10)+ 'SELECT '+char(10)+char(9)+@ColAll+char(10)+'From'+char(10)+char(9)+@TableName2, @DELETE='CREATE PROCEDURE d'+@TableName+char(10)+char(9)+'('+@Identity+')'+char(10)+'AS'+char(10)+'DELETE '+@TableName2+char(32)+'WHERE'+char(32)+@IdentityWHERE, @InputCol=CASE when exists(SELECT 1 from Sys.columns WHERE OBJECT_ID=@ObjectID and is_identity=1) then @Identity else '' end--InsertSELECT @Insert0='if OBJECT_ID('+quotename('c'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('c'+@TableName)+char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+'%%存储过程名:c'+@TableName+char(10)+char(10)+'%%输入参数:'+@ColName2+char(10)+char(10)+'%%输出参数:'+@InputCol+char(10)+char(10)+'%%功能:新增记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',--UPDATE@UPDATE0='if OBJECT_ID('+quotename('u'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('u'+@TableName)+char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+'%%存储过程名:u'+@TableName+char(10)+char(10)+'%%输入参数:'+@OutputAll+char(10)+char(10)+'%%输出参数:'+char(10)+char(10)+'%%功能:修改记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',--SELECTWHERE@SELECTWHERE0='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(32),@Identity)-1),1,1,''))+char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+'%%存储过程名:r'+@TableName+'By'+stuff(left(@Identity,charindex(char(32),@Identity)-1),1,1,'')+char(10)+char(10)+N'%%输入参数:'+@Identity+char(10)+char(10)+N'%%输出参数:'+char(10)+char(10)+N'%%功能:根据条件读取记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',--SELECT @SELECT0='if OBJECT_ID('+quotename('r'+@TableName+'ForAll','''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('r'+@TableName+'ForAll')+char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+'%%存储过程名:r'+@TableName+'ForAll'+char(10)+char(10)+'%%输入参数:'+char(10)+char(10)+'%%输出参数:'+char(10)+char(10)+'%%功能:读取记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/',@DELETE='if OBJECT_ID('+quotename('d'+@TableName,'''')+',''P'') is not null'+char(13)+char(9)+'DROP PROC '+quotename('d'+@TableName)+char(10)+'Go'+char(13)+char(10)+'/'+replicate('*',160)+char(10)+'%%存储过程名:d'+@TableName+char(10)+char(10)+'%%输入参数:'+char(10)+char(10)+'%%输出参数:'+@Identity+char(10)+char(10)+'%%功能:删除记录在表'+@TableName+char(10)+replicate('*',160)+char(10)+'%%编写:'+@Write+char(10)+char(10)+replicate('*',160)+'/'+char(10)+@DELETE+char(13)+char(10)+'Go'+char(13)+char(10)print 'USE '+db_name()+char(13)+char(10)+'Go'+char(13)+char(10)PRINT @Insert0print @Insert1print @Insert2print @Insert3PRINT 'Go'+char(13)+char(10)print @UPDATE0print @UPDATE1print @UPDATE2print @UPDATE3PRINT 'Go'+char(13)+char(10)print @SELECTWHERE0print @SELECTWHERE1PRINT 'Go'+char(13)+char(10)print @SELECT0print @SELECT1PRINT 'Go'+char(13)+char(10)print @DELETEgoexec sp_ms_marksystemobject 'CurdSqlString'--添加在系统存储过程目录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、调用方法:

--调用方法:exec CurdSqlString 'Tab1'-- 表名

显示效果:

USE testGoif OBJECT_ID('cTab1','P') is not null	DROP PROC [cTab1]Go/****************************************************************************************************************************************************************%%存储过程名:cTab1%%输入参数:@ID,@Name%%输出参数:%%功能:新增记录在表Tab1****************************************************************************************************************************************************************%%编写:Roy  2016-05-06****************************************************************************************************************************************************************/CREATE PROCEDURE cTab1		(@ID bigint,@Name sysname)ASINSERT INTO dbo.Tab1	([ID],[Name])VALUES	(@ID,@Name)Goif OBJECT_ID('uTab1','P') is not null	DROP PROC [uTab1]Go/****************************************************************************************************************************************************************%%存储过程名:uTab1%%输入参数:@ID,@Name%%输出参数:%%功能:修改记录在表Tab1****************************************************************************************************************************************************************%%编写:Roy  2016-05-06****************************************************************************************************************************************************************/CREATE PROCEDURE uTab1		(@ID bigint,@Name sysname)ASUPDATE dbo.Tab1SET [Name]=@NameWHERE [ID]=@IDGoif OBJECT_ID('rTab1ByID','P') is not null	DROP PROC [rTab1ByID]Go/****************************************************************************************************************************************************************%%存储过程名:rTab1ByID%%输入参数:@ID bigint%%输出参数:%%功能:根据条件读取记录在表Tab1****************************************************************************************************************************************************************%%编写:Roy  2016-05-06****************************************************************************************************************************************************************/CREATE PROCEDURE rTab1ByID		(@ID bigint)ASSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT 	[ID],[Name]From	dbo.Tab1WHERE	[ID]=@IDGoif OBJECT_ID('rTab1ForAll','P') is not null	DROP PROC [rTab1ForAll]Go/****************************************************************************************************************************************************************%%存储过程名:rTab1ForAll%%输入参数:%%输出参数:%%功能:读取记录在表Tab1****************************************************************************************************************************************************************%%编写:Roy  2016-05-06****************************************************************************************************************************************************************/CREATE PROCEDURE rTab1ForAllASSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT 	[ID],[Name]From	dbo.Tab1Goif OBJECT_ID('dTab1','P') is not null	DROP PROC [dTab1]Go/****************************************************************************************************************************************************************%%存储过程名:dTab1%%输入参数:%%输出参数:@ID bigint%%功能:删除记录在表Tab1****************************************************************************************************************************************************************%%编写:Roy  2016-05-06****************************************************************************************************************************************************************/CREATE PROCEDURE dTab1	(@ID bigint)ASDELETE dbo.Tab1 WHERE [ID]=@IDGo