T-SQL CRUD procedures auto-generator, Sql Server

T-SQL CRUD procedures auto-generator

A script to auto-generate basic CRUD stored procedures.

Published on What the # do I know.

https://zoharpeled.wordpress.com/2020/02/06/t-sql-crud-procedures-auto-generator/

CREATE TABLE Test
(
    Id int identity(1,1) CONSTRAINT PK_Test PRIMARY KEY,
    FloatCol float,
    NumericCol numeric(10, 2),
    DecimalCol Decimal(5, 1),
    Datetime2Col5 DateTime2(5),
    Datetime2Col7 DateTime2(7),
    TimeCol0 Time(1),
    datetimeoffsetCol datetimeoffset(3)
);

DECLARE @TableName sysname = 'Test' ;

BEGIN
 -- Declare variables
DECLARE @Sql nvarchar(max),
		@Lf nchar(2) = NCHAR(10),
		@FullParamsList nvarchar(max),
		@PkParamsList nvarchar(max),
		@PkWhereClause nvarchar(max),
		@Tab nchar(1) = NCHAR(9);

DECLARE @ColumnsList AS TABLE
(
	ColumnName sysname,
	TypeName sysname,
	ColMaxLength nvarchar(11),
	IncludeMaxLength bit,
	IsPkColumn bit,
	IsIdentity bit,
	IsComputed bit,
	IsAutoGenerated bit,
	IncludePrecisionAndScale bit,
	[Precision] nvarchar(3),
	IncludeScale bit,
	Scale nvarchar(3)
)

END -- Declare variables

BEGIN
 -- Populate variables
INSERT INTO @ColumnsList (
	ColumnName, 
	TypeName, 
	ColMaxLength, 
	IncludeMaxLength, 
	IsPkColumn, 
	IsIdentity, 
	IsComputed, 
	IsAutoGenerated,
	IncludePrecisionAndScale,
	[Precision],
	IncludeScale,
	Scale
)
SELECT  col.Name, 
		typ.Name,
		CAST(col.max_length as nvarchar(11)),
		IIF(typ.name IN ('nvarchar', 'nchar', 'varchar', 'char', 'varbinary', 'binary'), 1, 0),
		IIF(idx.is_primary_key = 1, 1, 0),
		col.is_identity,
		col.is_computed,
		SIGN(/*col.generated_always_type*/0), -- Rextester use an old version of SQL Server, that doesn't support this yet.
		IIF(typ.name IN('numeric', 'decimal'), 1, 0),
		col.[precision],
		IIF(typ.name IN('datetimeoffset', 'datetime2', 'time'), 1, 0),
		col.scale
FROM sys.columns as col
JOIN sys.types As typ
	ON  col.system_type_id = typ.system_type_id 
	AND col.user_type_id = typ.user_type_id 
JOIN sys.tables as tab
	ON col.object_id = tab.object_id 
LEFT JOIN sys.index_columns idxCol
	ON  col.object_id = idxCol.object_id 
	AND idxCol.column_id = col.column_id
LEFT JOIN sys.indexes idx
	ON  idx.object_id = tab.object_id 
	AND idxCol.index_id = idx.index_id
WHERE tab.name = @TableName

SELECT @FullParamsList = 
STUFF((
	SELECT CONCAT(@Tab, N',@' ,ColumnName , N' ', TypeName, 
		CASE WHEN IncludeMaxLength = 1 THEN 
			N'('+ IIF(ColMaxLength = '-1', N'max', CAST(IIF(TypeName LIKE 'n%', ColMaxLength / 2, ColMaxLength) as nvarchar(11))) + N')'
		WHEN IncludePrecisionAndScale = 1 THEN
			N'('+ [Precision] +N', '+ Scale +N')'
		WHEN IncludeScale = 1 THEN
			N'('+ Scale +N')'
		ELSE 
			''
		END, @Lf)
	FROM @ColumnsList
	WHERE IsComputed = 0
	AND IsAutoGenerated = 0
	FOR XML PATH('')
), 1, 2, @Tab + ' ') + N')'

SELECT @PkParamsList = 
	 STUFF((
		SELECT CONCAT(@Tab, N',@' ,ColumnName , N' ', TypeName, 
			CASE WHEN IncludeMaxLength = 1 
			THEN N'('+ IIF(ColMaxLength = '-1', N'max', CAST(IIF(TypeName LIKE 'n%', ColMaxLength / 2, ColMaxLength) as nvarchar(11))) + N')'
			ELSE ''
			END, @Lf)
		FROM @ColumnsList
		WHERE IsPkColumn = 1
		FOR XML PATH('')
	), 1, 2, @Tab + ' ') + N')'

SELECT @PkWhereClause = N'WHERE'+ 
	STUFF((
		SELECT CONCAT(N'AND ', QUOTENAME(ColumnName), N' = @', ColumnName, @Lf) 
		FROM @ColumnsList
		WHERE IsPkColumn = 1
		FOR XML PATH('')
	), 1, 4, ' ' )

END -- Populate variables

BEGIN
 -- create insert procedure
SELECT @Sql = N'CREATE PROCEDURE stp_'+ @TableName +N'_Insert ('+ @Lf +  
	@FullParamsList + @Lf + 
	N'AS' + @Lf +
	-- insert
	N'INSERT INTO '+ QUOTENAME(@TableName) + 
	STUFF((
		SELECT CONCAT(@Tab, N',', QUOTENAME(ColumnName), @Lf) 
		FROM @ColumnsList
		WHERE IsIdentity = 0
		AND IsComputed = 0
		AND IsAutoGenerated = 0
		FOR XML PATH('')
	), 1, 2, N' ('+ @Lf + @Tab +N' ') + ')' + @Lf + 
	-- Values
	STUFF((
		SELECT CONCAT(@Tab, N',@', ColumnName, @Lf) 
		FROM @ColumnsList
		WHERE IsIdentity = 0
		AND IsComputed = 0
		AND IsAutoGenerated = 0
		FOR XML PATH('')
	), 1, 2, N'VALUES ('+ @Lf + @Tab +N' ' ) + ')' + @Lf + N'GO' + @Lf

SELECT @Sql AS [Insert]
END -- create insert procedure

BEGIN
-- create update procedre
SELECT @Sql = N'CREATE PROCEDURE stp_'+ @TableName +N'_Update ('+ @Lf + 
	@FullParamsList + @Lf + 
	N'AS' + @Lf +
	N'UPDATE '+ QUOTENAME(@TableName) + @Lf +
	N'SET '+ 
	STUFF((
		SELECT CONCAT(@Tab, N',', QUOTENAME(ColumnName), N' = @', ColumnName, @Lf) 
		FROM @ColumnsList
		WHERE IsIdentity = 0
		AND IsPkColumn = 0
		AND IsComputed = 0
		FOR XML PATH('')
	), 1, 2, '' ) +
	@PkWhereClause + @Lf + 
	N'GO' + @Lf

SELECT @Sql AS [Update]
END -- create update procedre

BEGIN
-- create delete procedre
SELECT @Sql = N'CREATE PROCEDURE stp_'+ @TableName +N'_Delete ('+ @Lf + 
	@PkParamsList  + @Lf +
	N'AS' + @Lf +
	N'DELETE'+ @Lf +
	N'FROM '+ QUOTENAME(@TableName) + @Lf +
	@PkWhereClause + @Lf + 
	N'GO' + @Lf

SELECT @Sql AS [Delete]
END  -- create delete procedre

BEGIN
-- create select procedre
SELECT @Sql = N'CREATE PROCEDURE stp_'+ @TableName +N'_Select ('+ @Lf + 
	@PkParamsList  + @Lf +
	N'AS' + @Lf +
	N'SELECT '+
	STUFF((
		SELECT CONCAT(N',', QUOTENAME(ColumnName) + @Lf + @Tab + @Tab) 
		FROM @ColumnsList
		FOR XML PATH('')
	), 1, 1, ' ') + 
	N'FROM '+ QUOTENAME(@TableName) + @Lf +
	@PkWhereClause + @Lf + 
	N'GO' + @Lf

SELECT @Sql AS [Select]
END  -- create select procedre


run \| edit \| history \| help	0