loading...

T-SQL CRUD procedures auto-generator

peledzohar profile image Zohar Peled ・4 min read

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 - 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:

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 F5, and get this output printed on the Messages window:

-- 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:

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

Posted on by:

peledzohar profile

Zohar Peled

@peledzohar

By day, try to work. By night, try to sleep.

Discussion

pic
Editor guide
 

It's an interesting exercise in code generation, but I'm more curious about the why: what's going on that you need to perform basic CRUD through procedures instead of emitting the SQL statements directly?

 

In my workplace we don't use sophisticated ORMs. The ORM of choice is Dapper, and this means that we need to write the SQL ourselves. (Which personally, I like because it gives me a lot of control over the SQL). Admittedly, a lot of the SQL we need we will not get out of the box with this script - but as I've written in the full article - that's not what this script is supposed to do anyway.