Stored Procedure Field Generator
Field generator creates a number of code blocks for SQL Server Stored Procedures, insert, update, delete and variables.
Save time by copying relevant parts
Coding can take time, but we realised we use a number of types of code blocks regularly, be it to update a table through a stored procedure, or return data back.
This function below can generate he majority of the fields required to update a table for us, all we need to then do is copy and paste the relevant blocks into a new query window and we are done. The declaration part saves a huge amount of time for us in particular.
This won't be perfect for every situation, and can be adapted to suit your coding style.
SQL
USE Utilities
GO
CREATE PROC GetProcCommands(@Database NVARCHAR(200),@Schema NVARCHAR(200),@Table NVARCHAR(200)) AS BEGIN
DECLARE @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.name
WHEN ''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) ColumnDataType
FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.all_columns c
LEFT JOIN (SELECT c.object_id,c.index_id,c.column_id,is_primary_key
FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.indexes i
INNER JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.index_columns c ON i.object_id=c.object_id AND i.index_id=c.index_id
WHERE 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_id
LEFT JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.types t ON t.user_type_id=c.user_type_id AND t.system_type_id=c.system_type_id
WHERE c.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''')'
INSERT INTO @Cols
EXEC 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+' '+ColumnDataType
FROM @Cols
SELECT @ColumnList_Insert=COALESCE(@ColumnList_Insert+',','')+ColumnName,
@ColumnList_Stored_Insert=COALESCE(@ColumnList_Stored_Insert+',','')+'@'+ColumnName
FROM @Cols
WHERE ColumnIdentity=0
SELECT @ColumnList_Update=COALESCE(@ColumnList_Update+',','')+ColumnName+'=@'+ColumnName
FROM @Cols
WHERE ColumnIdentity=0 AND ISNULL(ColumnPrimaryKey,0)=0
SELECT @ColumnList_Update_Where=COALESCE(@ColumnList_Update_Where+' AND ','')+ColumnName+'=@'+ColumnName,
@ColumnList_Stored_PK=COALESCE(@ColumnList_Stored_PK+',','')+'@'+ColumnName+' '+ColumnDataType
FROM @Cols
WHERE ISNULL(ColumnPrimaryKey,0)=1
SELECT @ColumnList_Stored_CheckNotNull=COALESCE(@ColumnList_Stored_CheckNotNull+' AND ','')+'@'+ColumnName+' IS NOT NULL',
@ColumnList_Stored_CheckNull=COALESCE(@ColumnList_Stored_CheckNull+' AND ','')+'@'+ColumnName+' IS NULL'
FROM @Cols
WHERE ISNULL(ColumnPrimaryKey,0)=1
SELECT 'Create Proc Upd' FieldType,
@ColumnList_Stored Fields,
'CREATE PROC [Upd_'+@Table+']('+@ColumnList_Stored+') AS BEGIN' Command
UNION
SELECT 'Create Proc Del' FieldType,
@ColumnList_Stored_PK Fields,
'CREATE PROC [Del_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' Command
UNION
SELECT 'Create Proc Get' FieldType,
@ColumnList_Stored_PK Fields,
'CREATE PROC [Get_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' Command
UNION
SELECT 'Declare Variables' FieldType,
@ColumnList_Stored Fields,
'DECLARE '+@ColumnList_Stored+'' Command
UNION
SELECT 'Select' FieldType,
@ColumnList_Select Fields,
'SELECT '+@ColumnList_Select Command
UNION
SELECT 'Insert Fields' FieldType,
@ColumnList_Insert Fields,
'INSERT INTO '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+'('+@ColumnList_Insert+')' Command
UNION
SELECT 'Insert Variables' FieldType,
@ColumnList_Stored_Insert Fields,
'SELECT '+@ColumnList_Stored_Insert Command
UNION
SELECT 'Update' FieldType,
@ColumnList_Update Fields,
'SET '+@ColumnList_Update Command
UNION
SELECT 'Where Key=Variable' FieldType,
@ColumnList_Update_Where Fields,
'WHERE '+@ColumnList_Update_Where Command
UNION
SELECT 'From' FieldType,
'' Fields,
'FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') Command
UNION
SELECT 'Update Table' FieldType,
'' Fields,
'UPDATE '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') Command
UNION
SELECT 'Check Not Null' FieldType,
'' Fields,
'IF '+@ColumnList_Stored_CheckNotNull+' BEGIN' Command
UNION
SELECT 'Check Null' FieldType,
'' Fields,
'IF '+@ColumnList_Stored_CheckNull+' BEGIN' Command
END
GO
GO
CREATE PROC GetProcCommands(@Database NVARCHAR(200),@Schema NVARCHAR(200),@Table NVARCHAR(200)) AS BEGIN
DECLARE @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.name
WHEN ''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) ColumnDataType
FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.all_columns c
LEFT JOIN (SELECT c.object_id,c.index_id,c.column_id,is_primary_key
FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.indexes i
INNER JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.index_columns c ON i.object_id=c.object_id AND i.index_id=c.index_id
WHERE 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_id
LEFT JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.types t ON t.user_type_id=c.user_type_id AND t.system_type_id=c.system_type_id
WHERE c.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''')'
INSERT INTO @Cols
EXEC 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+' '+ColumnDataType
FROM @Cols
SELECT @ColumnList_Insert=COALESCE(@ColumnList_Insert+',','')+ColumnName,
@ColumnList_Stored_Insert=COALESCE(@ColumnList_Stored_Insert+',','')+'@'+ColumnName
FROM @Cols
WHERE ColumnIdentity=0
SELECT @ColumnList_Update=COALESCE(@ColumnList_Update+',','')+ColumnName+'=@'+ColumnName
FROM @Cols
WHERE ColumnIdentity=0 AND ISNULL(ColumnPrimaryKey,0)=0
SELECT @ColumnList_Update_Where=COALESCE(@ColumnList_Update_Where+' AND ','')+ColumnName+'=@'+ColumnName,
@ColumnList_Stored_PK=COALESCE(@ColumnList_Stored_PK+',','')+'@'+ColumnName+' '+ColumnDataType
FROM @Cols
WHERE ISNULL(ColumnPrimaryKey,0)=1
SELECT @ColumnList_Stored_CheckNotNull=COALESCE(@ColumnList_Stored_CheckNotNull+' AND ','')+'@'+ColumnName+' IS NOT NULL',
@ColumnList_Stored_CheckNull=COALESCE(@ColumnList_Stored_CheckNull+' AND ','')+'@'+ColumnName+' IS NULL'
FROM @Cols
WHERE ISNULL(ColumnPrimaryKey,0)=1
SELECT 'Create Proc Upd' FieldType,
@ColumnList_Stored Fields,
'CREATE PROC [Upd_'+@Table+']('+@ColumnList_Stored+') AS BEGIN' Command
UNION
SELECT 'Create Proc Del' FieldType,
@ColumnList_Stored_PK Fields,
'CREATE PROC [Del_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' Command
UNION
SELECT 'Create Proc Get' FieldType,
@ColumnList_Stored_PK Fields,
'CREATE PROC [Get_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' Command
UNION
SELECT 'Declare Variables' FieldType,
@ColumnList_Stored Fields,
'DECLARE '+@ColumnList_Stored+'' Command
UNION
SELECT 'Select' FieldType,
@ColumnList_Select Fields,
'SELECT '+@ColumnList_Select Command
UNION
SELECT 'Insert Fields' FieldType,
@ColumnList_Insert Fields,
'INSERT INTO '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+'('+@ColumnList_Insert+')' Command
UNION
SELECT 'Insert Variables' FieldType,
@ColumnList_Stored_Insert Fields,
'SELECT '+@ColumnList_Stored_Insert Command
UNION
SELECT 'Update' FieldType,
@ColumnList_Update Fields,
'SET '+@ColumnList_Update Command
UNION
SELECT 'Where Key=Variable' FieldType,
@ColumnList_Update_Where Fields,
'WHERE '+@ColumnList_Update_Where Command
UNION
SELECT 'From' FieldType,
'' Fields,
'FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') Command
UNION
SELECT 'Update Table' FieldType,
'' Fields,
'UPDATE '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') Command
UNION
SELECT 'Check Not Null' FieldType,
'' Fields,
'IF '+@ColumnList_Stored_CheckNotNull+' BEGIN' Command
UNION
SELECT 'Check Null' FieldType,
'' Fields,
'IF '+@ColumnList_Stored_CheckNull+' BEGIN' Command
END
GO
Test
CREATE TABLE ProcCommandsTest(TestID INT IDENTITY(1,1) CONSTRAINT PK_TestID PRIMARY KEY,TestCol1 NVARCHAR(MAX),TestCol2 DECIMAL(18,2))
GO
EXEC Utilities..GetProcCommands '','','ProcCommandsTest'
GO
EXEC Utilities..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 |
Hope this helps you out, happy coding!