SQL Server開発インターフェース生成方法
17959 ワード
開発効率を高めるためには、固定フォーマットを生成するインターフェースが必要であり、以下の例では、新規/修正/削除/読み取りインターフェースを提供します。
一般的なテーブル構造を例にとって、特殊なテーブル構造は自分で方法を調整してみてもいいです。
1、Testデータベースでの生成方法
2、表Tab 1を作成する
e.g.
一般的なテーブル構造を例にとって、特殊なテーブル構造は自分で方法を調整してみてもいいです。
1、Testデータベースでの生成方法
if OBJECT_ID('Curdsqlstring','P') is not null
DROP PROC Curdsqlstring
go
CREATE PROCEDURE Curdsqlstring(
@TableName sysname
)
with encryption
AS
if 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 sysname
SELECT @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+',','')+'@'+Name
from
Sys.columns AS a
WHERE
a.OBJECT_ID=OBJECT_ID(@TableName)
order by CASE when Name='ID' then 0 else 1 end,Column_id
IF @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_id
IF @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 END
SELECT @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
--Insert
SELECT @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 @Insert0
print @Insert1
print @Insert2
print @Insert3
PRINT 'Go'+char(13)+char(10)
print @UPDATE0
print @UPDATE1
print @UPDATE2
print @UPDATE3
PRINT 'Go'+char(13)+char(10)
print @SELECTWHERE0
print @SELECTWHERE1
PRINT 'Go'+char(13)+char(10)
print @SELECT0
print @SELECT1
PRINT 'Go'+char(13)+char(10)
print @DELETE
go
exec sp_ms_marksystemobject 'CurdSqlString'--
go
2、表Tab 1を作成する
e.g.
USE [test]
GO
/****** Object: Table [dbo].[Tab1] Script Date: 2016/5/6 11:51:47 ******/
IF OBJECT_ID('Tab1','U') IS NOT NULL
DROP TABLE [dbo].[Tab1]
GO
CREATE TABLE [dbo].[Tab1](
[ID] [BIGINT] NULL,
[Name] [sysname] NOT NULL
) ON [PRIMARY]
GO-- :
exec CurdSqlString 'Tab1'--
USE test
Go
if 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)
AS
INSERT INTO dbo.Tab1
([ID],[Name])
VALUES
(@ID,@Name)
Go
if 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)
AS
UPDATE dbo.Tab1
SET [Name]=@Name
WHERE [ID]=@ID
Go
if OBJECT_ID('rTab1ByID','P') is not null
DROP PROC [rTab1ByID]
Go
/****************************************************************************************************************************************************************
%% ：rTab1ByID
%% ：@ID bigint
%% ：
%% ： Tab1
****************************************************************************************************************************************************************
%% ：Roy 2016-05-06
****************************************************************************************************************************************************************/
CREATE PROCEDURE rTab1ByID
(@ID bigint)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
[ID],[Name]
From
dbo.Tab1
WHERE
[ID]=@ID
Go
if OBJECT_ID('rTab1ForAll','P') is not null
DROP PROC [rTab1ForAll]
Go
/****************************************************************************************************************************************************************
%% ：rTab1ForAll
%% ：
%% ：
%% ： Tab1
****************************************************************************************************************************************************************
%% ：Roy 2016-05-06
****************************************************************************************************************************************************************/
CREATE PROCEDURE rTab1ForAll
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
[ID],[Name]
From
dbo.Tab1
Go
if OBJECT_ID('dTab1','P') is not null
DROP PROC [dTab1]
Go
/****************************************************************************************************************************************************************
%% ：dTab1
%% ：
%% ：@ID bigint
%% ： Tab1
****************************************************************************************************************************************************************
%% ：Roy 2016-05-06
****************************************************************************************************************************************************************/
CREATE PROCEDURE dTab1
(@ID bigint)
AS
DELETE dbo.Tab1 WHERE [ID]=@ID
Go