ストアド プロシージャ フィールド ジェネレーター
フィールド ジェネレーターは、SQL Server ストアド プロシージャ、挿入、更新、削除、および変数用の多数のコード ブロックを作成します。
関連部分をコピーして時間を節約
コーディングには時間がかかる場合がありますが、ストアド プロシージャを介してテーブルを更新したり、データを返したりするために、さまざまな種類のコード ブロックを定期的に使用していることに気付きました。
以下のこの関数は、テーブルを更新するために必要なフィールドの大部分を生成できます。その後、関連するブロックをコピーして新しいクエリ ウィンドウに貼り付けるだけで完了です。宣言部分は、特に私たちにとって膨大な時間を節約します。
これはすべての状況に最適というわけではなく、コーディング スタイルに合わせて調整できます。
SQL
CREATE PROC GetProcCommands(@Database NVARCHAR(200),@Schema NVARCHAR(200),@Table NVARCHAR(200)) AS BEGINDECLARE @Cols TABLE(ColumnID INT NOT NULL,ColumnName nvarchar(128) NULL,ColumnPrimaryKey BIT NULL,ColumnIdentity BIT NULL,ColumnDataType nvarchar (193) NULL)
DECLARE @SQL NVARCHAR(MAX)='SELECT c.column_id,c.name,is_primary_key,c.is_identity,t.name +(Case t.nameWHEN ''sql_variant'' Then '''' WHEN ''text'' Then '''' WHEN ''ntext'' Then '''' WHEN ''uniqueidentifier'' Then '''' WHEN ''xml'' Then '''' WHEN ''real'' Then ''''WHEN ''int'' Then '''' WHEN ''bigint'' Then '''' WHEN ''smallint'' Then '''' WHEN ''tinyint'' Then '''' WHEN ''money'' THEN '''' WHEN ''float'' THEN ''''WHEN ''datetime'' THEN '''' WHEN ''date'' THEN '''' WHEN ''datetime2'' THEN '''' WHEN ''sysname'' THEN '''' WHEN ''bit'' THEN '''' WHEN ''image'' THEN ''''WHEN ''datetimeoffset'' Then ''('' + Cast( c.scale As varchar ) + '') ''WHEN ''decimal'' Then ''('' + Cast( c.precision As varchar ) + '', '' + Cast( c.scale As varchar ) + '') ''Else Coalesce( ''('' + Case WHEN c.max_length = -1 Then ''MAX'' Else Cast( c.max_length As VarChar) End + '')'' , '''')End) ColumnDataTypeFROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.all_columns cLEFT JOIN (SELECT c.object_id,c.index_id,c.column_id,is_primary_keyFROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.indexes iINNER JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.index_columns c ON i.object_id=c.object_id AND i.index_id=c.index_idWHERE i.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''') AND is_primary_key=1) i ON i.object_id=c.object_id AND i.column_id=c.column_idLEFT JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.types t ON t.user_type_id=c.user_type_id AND t.system_type_id=c.system_type_idWHERE c.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''')'
INSERT INTO @ColsEXEC sp_executesql @SQL
DECLARE @ColumnList_Select NVARCHAR(MAX),@ColumnList_Insert NVARCHAR(MAX),@ColumnList_Update NVARCHAR(MAX),@ColumnList_Update_Where NVARCHAR(MAX),@ColumnList_Stored NVARCHAR(MAX),@ColumnList_Stored_PK NVARCHAR(MAX),@ColumnList_Stored_Insert NVARCHAR(MAX),@ColumnList_Stored_CheckNull NVARCHAR(MAX),@ColumnList_Stored_CheckNotNull NVARCHAR(MAX)SELECT @ColumnList_Select = COALESCE(@ColumnList_Select+',','')+ColumnName,@ColumnList_Stored=COALESCE(@ColumnList_Stored+',','')+'@'+ColumnName+' '+ColumnDataTypeFROM @ColsSELECT @ColumnList_Insert=COALESCE(@ColumnList_Insert+',','')+ColumnName,@ColumnList_Stored_Insert=COALESCE(@ColumnList_Stored_Insert+',','')+'@'+ColumnName FROM @ColsWHERE ColumnIdentity=0 SELECT @ColumnList_Update=COALESCE(@ColumnList_Update+',','')+ColumnName+'=@'+ColumnNameFROM @ColsWHERE ColumnIdentity=0 AND ISNULL(ColumnPrimaryKey,0)=0SELECT @ColumnList_Update_Where=COALESCE(@ColumnList_Update_Where+' AND ','')+ColumnName+'=@'+ColumnName,@ColumnList_Stored_PK=COALESCE(@ColumnList_Stored_PK+',','')+'@'+ColumnName+' '+ColumnDataTypeFROM @Cols WHERE ISNULL(ColumnPrimaryKey,0)=1SELECT @ColumnList_Stored_CheckNotNull=COALESCE(@ColumnList_Stored_CheckNotNull+' AND ','')+'@'+ColumnName+' IS NOT NULL',@ColumnList_Stored_CheckNull=COALESCE(@ColumnList_Stored_CheckNull+' AND ','')+'@'+ColumnName+' IS NULL'FROM @ColsWHERE ISNULL(ColumnPrimaryKey,0)=1SELECT 'Create Proc Upd' FieldType,@ColumnList_Stored Fields,'CREATE PROC [Upd_'+@Table+']('+@ColumnList_Stored+') AS BEGIN' CommandUNION SELECT 'Create Proc Del' FieldType,@ColumnList_Stored_PK Fields,'CREATE PROC [Del_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' CommandUNIONSELECT 'Create Proc Get' FieldType,@ColumnList_Stored_PK Fields,'CREATE PROC [Get_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' CommandUNION SELECT 'Declare Variables' FieldType,@ColumnList_Stored Fields,'DECLARE '+@ColumnList_Stored+'' CommandUNIONSELECT 'Select' FieldType,@ColumnList_Select Fields,'SELECT '+@ColumnList_Select CommandUNIONSELECT 'Insert Fields' FieldType,@ColumnList_Insert Fields,'INSERT INTO '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+'('+@ColumnList_Insert+')' CommandUNIONSELECT 'Insert Variables' FieldType,@ColumnList_Stored_Insert Fields,'SELECT '+@ColumnList_Stored_Insert CommandUNIONSELECT 'Update' FieldType,@ColumnList_Update Fields,'SET '+@ColumnList_Update CommandUNIONSELECT 'Where Key=Variable' FieldType,@ColumnList_Update_Where Fields,'WHERE '+@ColumnList_Update_Where CommandUNIONSELECT 'From' FieldType,'' Fields,'FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') CommandUNION SELECT 'Update Table' FieldType,'' Fields,'UPDATE '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') CommandUNION SELECT 'Check Not Null' FieldType,'' Fields,'IF '+@ColumnList_Stored_CheckNotNull+' BEGIN' CommandUNIONSELECT 'Check Null' FieldType,'' Fields,'IF '+@ColumnList_Stored_CheckNull+' BEGIN' CommandENDGOTest it
CREATE TABLE ProcCommandsTest(TestID INT IDENTITY(1,1) CONSTRAINT PK_TestID PRIMARY KEY,TestCol1 NVARCHAR(MAX),TestCol2 DECIMAL(18,2))GOEXEC GetProcCommands '','' ,'ProcCommandsTest'Output
| FieldType | Fields | Command |
|---|---|---|
| Check Not Null | IF @TestID IS NOT NULL BEGIN | |
| Check Null | IF @TestID IS NULL BEGIN | |
| Create Proc Del | @TestID int | CREATE PROC [Del_ProcCommandsTest](@TestID int) AS BEGIN |
| Create Proc Get | @TestID int | CREATE PROC [Get_ProcCommandsTest](@TestID int) AS BEGIN |
| Create Proc Upd | @TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) | CREATE PROC [Upd_ProcCommandsTest](@TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) ) AS BEGIN |
| Declare Variables | @TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) | DECLARE @TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) |
| From | FROM ProcCommandsTest | |
| Insert Fields | TestCol1,TestCol2 | INSERT INTO ProcCommandsTest(TestCol1,TestCol2) |
| Insert Variables | @TestCol1,@TestCol2 | SELECT @TestCol1,@TestCol2 |
| Select | TestID,TestCol1,TestCol2 | SELECT TestID,TestCol1,TestCol2 |
| Update | TestCol1=@TestCol1,TestCol2=@TestCol2 | SET TestCol1=@TestCol1,TestCol2=@TestCol2 |
| Update Table | UPDATE ProcCommandsTest | |
| Where Key=Variable | TestID=@TestID | WHERE TestID=@TestID |
関連部分をコピーして時間を節約
これがお役に立てば幸いです。コーディングをお楽しみください。
