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
Top comments (2)
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.