T-SQL CRUD procedures auto-generator
Tags: sql, database
Published: 2020-02-06 00:06:15

Are you tired of manually writing simple CRUD stored procedures? If so, this is the post for you!

It's getting very late here (2 AM) and I've just [posted this on What the # Do I know](https://zoharpeled.wordpress.com/2020/02/06/t-sql-crud-procedures-auto-generator/) - but I'm simply too tired to even copy & paste the full article (and it's quite long, too).

So I'm not going to do that, but just let you know that the full post is out there, with a script that takes in a table name and auto generate insert, update, delete and select statements for that table, and a link to a live demo on rextester.

I am going to share the example here, as well as the full script - but if you want to read more about it, you'll have to go to the source, sorry...


So, Given the following test table:

```sql
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)
);
```

You enter the table's name, make sure you run on the correct database, hit <kbd>F5</kbd>, and get this output printed on the Messages window:

```sql
-- Insert:
  
 CREATE PROCEDURE stp_Test_Insert (
     @Id int
    ,@FloatCol float
    ,@NumericCol numeric(10, 2)
    ,@DecimalCol decimal(5, 1)
    ,@Datetime2Col5 datetime2(5)
    ,@Datetime2Col7 datetime2(7)
    ,@TimeCol0 time(1)
    ,@datetimeoffsetCol datetimeoffset(3)
 )
 AS
 INSERT INTO [Test] (
     [FloatCol]
    ,[NumericCol]
    ,[DecimalCol]
    ,[Datetime2Col5]
    ,[Datetime2Col7]
    ,[TimeCol0]
    ,[datetimeoffsetCol]
 )
 VALUES (
     @FloatCol
    ,@NumericCol
    ,@DecimalCol
    ,@Datetime2Col5
    ,@Datetime2Col7
    ,@TimeCol0
    ,@datetimeoffsetCol
 )
 GO
  
  
-- Update:
  
 CREATE PROCEDURE stp_Test_Update (
     @Id int
    ,@FloatCol float
    ,@NumericCol numeric(10, 2)
    ,@DecimalCol decimal(5, 1)
    ,@Datetime2Col5 datetime2(5)
    ,@Datetime2Col7 datetime2(7)
    ,@TimeCol0 time(1)
    ,@datetimeoffsetCol datetimeoffset(3)
 )
 AS
 UPDATE [Test]
 SET [FloatCol] = @FloatCol
    ,[NumericCol] = @NumericCol
    ,[DecimalCol] = @DecimalCol
    ,[Datetime2Col5] = @Datetime2Col5
    ,[Datetime2Col7] = @Datetime2Col7
    ,[TimeCol0] = @TimeCol0
    ,[datetimeoffsetCol] = @datetimeoffsetCol
 WHERE [Id] = @Id
  
 GO
  
  
-- Delete:
  
 CREATE PROCEDURE stp_Test_Delete (
     @Id int
 )
 AS
 DELETE
 FROM [Test]
 WHERE [Id] = @Id
  
 GO
  
  
-- Select:
  
 CREATE PROCEDURE stp_Test_Select (
     @Id int
 )
 AS
 SELECT  [Id]
        ,[FloatCol]
        ,[NumericCol]
        ,[DecimalCol]
        ,[Datetime2Col5]
        ,[Datetime2Col7]
        ,[TimeCol0]
        ,[datetimeoffsetCol]
        FROM [Test]
 WHERE [Id] = @Id
  
 GO
```

As promised, here's the full script:

```sql
USE <DatabaseName> -- Change that to your database name
GO
 
DECLARE @TableName sysname = '<TableName>' -- not dbo.tableName, just tableName
 
 
 
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)
);
 
DECLARE @WritableColumns AS TABLE
(
    ColumnName sysname
);
 
 
 
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),
        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
 
INSERT INTO @WritableColumns (ColumnName)
SELECT ColumnName
FROM @ColumnsList
WHERE IsIdentity = 0
AND IsComputed = 0
AND IsAutoGenerated = 0;
 
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 @WritableColumns
        FOR XML PATH('')
    ), 1, 2, N' ('+ @Lf + @Tab +N' ') + ')' + @Lf + 
    -- Values
    STUFF((
        SELECT CONCAT(@Tab, N',@', ColumnName, @Lf) 
        FROM @WritableColumns
        FOR XML PATH('')
    ), 1, 2, N'VALUES ('+ @Lf + @Tab +N' ' ) + ')' + @Lf + N'GO' + @Lf 
 
PRINT @Lf + '-- Insert:'+ @Lf + @Lf + @Sql + @Lf 
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 @WritableColumns
        FOR XML PATH('')
    ), 1, 2, '' ) +
    @PkWhereClause + @Lf + 
    N'GO' + @Lf 
 
PRINT '-- Update:'+ @Lf + @Lf + @Sql + @Lf
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 
 
PRINT '-- Delete:'+ @Lf + @Lf + @Sql + @Lf
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 
 
PRINT '-- Select:'+ @Lf + @Lf + @Sql + @Lf
END  -- create select procedre
```