Generate SQL Update Statement And Stored Procedure From Table Name

Today I had to write a few simple update stored procedures and a couple of update statements. This got me thinking, seeing that I’m a SQL / .Net developer, I create loads of these every month.

I don’t like doing repetitive tasks so why not spend a little time and write a script that generate these for me? So here it goes, here are two scripts and they are available as is. I know there are some data types that might not work or get excluded or throw exception etc etc etc…for the bulk of my work this will work a charm and in any case I need to check before I compile them.

Note: Your database context needs to be the same for the stored procedure/update statement as well as the table in question.

Execution for Update Statement

EXEC [GenerateUpdateStatementForTable] @TableName = 'Offices'

Result

UPDATE 
 [Offices]
SET
 [OfficeID] = 0,[IF PK THEN REMOVE LINE]
 [SubRegion_ID] = 0,
 [Office] = '', 
 [Office] = 0
WHERE
 [WHERE CLAUSE GOES HERE]

Execution for Stored Procedure

EXEC [GenerateUpdateProcForTable] @TableName = 'Offices'

Result

CREATE PROC UpdateOffices
 @OfficeID SMALLINT,
 @SubRegion_ID INT,
 @Office NVARCHAR(50)
AS
UPDATE 
 [Offices]
SET
 [OfficeID] = @OfficeID [IF PK THEN REMOVE LINE],
 [SubRegion_ID] = @SubRegion_ID,
 [Office] = @Office
WHERE
 [WHERE CLAUSE GOES HERE]
Code For Stored Procedures
ALTER PROC [dbo].[GenerateUpdateProcForTable]
 @TableName SYSNAME 
AS
DECLARE @UpdateStatment VARCHAR(MAX) = ''
DECLARE @VarStatment VARCHAR(MAX) = ''
DECLARE @ColStatment VARCHAR(MAX) = ''
SET @UpdateStatment = @UpdateStatment + 'UPDATE ' + CHAR(13) + CHAR(9) + '[' + @TableName + ']' + CHAR(13) 
SET @UpdateStatment = @UpdateStatment + 'SET'  
SELECT
 @VarStatment = @VarStatment + CHAR(13) + CHAR(9) + '@' + [sys].[columns].[name] + ' ' + UPPER([sys].[systypes].[name]) + CASE WHEN [sys].[systypes].[name] LIKE '%char%' THEN CASE WHEN SUBSTRING([sys].[systypes].[name] , 1 , 1) = 'n' THEN '(' + CAST([sys].[columns].[max_length] / 2 AS VARCHAR(5)) + ')'
 ELSE '(' + CAST([sys].[columns].[max_length] AS VARCHAR(5)) + ')'
 END
 ELSE ''
 END + ','
 ,@ColStatment = @ColStatment + CHAR(13) + CHAR(9) + '[' + [sys].[columns].[name] + '] = @' + [sys].[columns].[name] + CASE WHEN [column_id] = 1 THEN ' [IF PK THEN REMOVE LINE]'
 ELSE ''
 END + ','
FROM
 [sys].[columns]
 INNER JOIN [sys].[systypes]
 ON [system_type_id] = [xtype]
WHERE
 [object_id] = OBJECT_ID(@TableName)
 AND [sys].[systypes].[name] != 'sysname'
ORDER BY
 [column_id]
IF @@ROWCOUNT = 0 
 BEGIN
 RAISERROR ('TABLE NOT FOUND',16,1)
 RETURN 
 END
SET @VarStatment = SUBSTRING(@VarStatment , 1 , LEN(@VarStatment) - 1)
SET @ColStatment = SUBSTRING(@ColStatment , 1 , LEN(@ColStatment) - 1)
SET @UpdateStatment ='CREATE PROC Update' + 
 @TableName + 
 @VarStatment + CHAR(13) + 
 'AS' + CHAR(13) + CHAR(13) + 
 @UpdateStatment + 
 @ColStatment + CHAR(13) + 
 'WHERE' + CHAR(13) + CHAR(9) + 
 '[WHERE CLAUSE GOES HERE]'
PRINT @UpdateStatment
Code for Update Statement
ALTER PROC [dbo].[GenerateUpdateStatementForTable]
@TableName SYSNAME
AS
DECLARE @UpdateStatment VARCHAR(MAX) = ''
DECLARE @ColStatment VARCHAR(MAX) = ''
SET @UpdateStatment = @UpdateStatment + 'UPDATE ' + CHAR(13) + CHAR(9) + '[' + @TableName + ']' + CHAR(13)
SET @UpdateStatment = @UpdateStatment + 'SET'
SELECT
@ColStatment = @ColStatment + CHAR(13) + CHAR(9) + '[' + [sys].[columns].[name] + ']' + ' =' + CASE WHEN [sys].[systypes].[name] LIKE '%char%'
OR [sys].[systypes].[name] LIKE '%date%' THEN ' '''', '
ELSE ' 0,'
END + CASE WHEN [column_id] = 1 THEN '[IF PK THEN REMOVE LINE]' ELSE '' END
FROM
[sys].[columns]
INNER JOIN [sys].[systypes]
ON [system_type_id] = [xtype]
WHERE
[object_id] = OBJECT_ID(@TableName)
AND [sys].[systypes].[name] != 'sysname'
IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('TABLE NOT FOUND',16,1)
RETURN
END
SET @ColStatment = SUBSTRING(@ColStatment , 1 , LEN(@ColStatment) - 1)
SET @UpdateStatment = @UpdateStatment + @ColStatment + CHAR(13) + 'WHERE'+ CHAR(13)  + CHAR(9) + '[WHERE CLAUSE GOES HERE]'
PRINT @UpdateStatment
Well I hope this helps someone, if you have any changes or improvements please send them to me and I will update and credit where appropiate