[T-SQL] Auto générer un template d’update

Dans la majorité des cas lorsque je crée un DataWarehouse avec SQL Server, j’utilise SSIS pour réaliser un SCD Type 1, ou 2 à base de lookups, d’OLEDB Destination lorsque les enregistrements n’existent pas et d’OLEDB Command* pour les mises à jour.

*Si ces changements arrivent peu souvent car le composant OLEDB Command est super lent, dans le cas d’un grand nombre de changements, il est préférable d’utiliser l’instruction T-SQL Merger

Ce qui nous donne quelque chose comme cela :

Or le composant OLEDB Command est beaucoup plus lisible lorsque qu’il est utilisé avec une procédure stockée qu’une instruction SQL. Il m’est donc nécessaire de générer pour toutes mes dimensions des scripts de création de procédures stockées afin de réaliser mes mises à jour.

Le résultat attendu est le suivant :

CREATE PROC UpdateDimRestaurant(
@RestaurantId int
,@RestaurantSkId int
,@Adresse1 varchar(45)
,@Adresse2 varchar(45)
,@CodePostal varchar(5)
,@RestaurantName varchar(30)
,@Interlocuteur varchar(30)
,@Ville varchar(45)
) AS BEGIN
UPDATE dw.DimRestaurant SET RestaurantSkId=@RestaurantSkId
,Adresse1=@Adresse1
,Adresse2=@Adresse2
,CodePostal=@CodePostal
,RestaurantName=@RestaurantName
,Interlocuteur=@Interlocuteur
,Ville=@Ville
WHERE RestaurantId=@RestaurantId
END

Pour éviter de le taper à la main j’utilise la fonction table suivante :

CREATE FUNCTION [dbo].[GenerateUpdate](@TableName varchar(255))
RETURNS TABLE
AS
RETURN
(
 WITH SysTableCTE AS
 (
 SELECT C.name AS ColumnName
 ,S.name AS TypeName
 ,C.collation_name
 ,C.max_length
 ,T.name AS TableName
 ,SC.name AS SchemaName
 ,ROW_NUMBER()OVER(ORDER BY C.COLUMN_ID) AS RowNumber
 FROM sys.tables T
 INNER JOIN sys.columns C
 ON C.object_id=T.object_id
 INNER JOIN sys.types S
 ON s.user_type_id=c.user_type_id
 INNER JOIN sys.schemas SC
 ON SC.schema_id = T.schema_id
 WHERE T.name = @TableName
 )
 ,concatParamCTE(Params) AS
 (
 SELECT CONCAT(',@'
 ,ColumnName
 ,' '
 ,TypeName
 ,CASE WHEN collation_name IS NOT NULL 
 THEN '('+CAST(max_length AS varchar)+')' 
 ELSE '' 
 END
 ,CHAR(10)
 )
 FROM SysTableCTE
 FOR XML PATH('')
 ),concatUpdate(Query) AS
 (
 SELECT CONCAT(','
 ,ColumnName
 ,'=@'
 ,ColumnName 
 ,CHAR(10) 
 )
 FROM SysTableCTE
 WHERE RowNumber > 1
 FOR XML PATH('')
 )

SELECT 'CREATE PROC Update'+S.TableName
 + '(' 
 + CHAR(10)
 + STUFF(Params,1,1,'') 
 + ') AS BEGIN'
 + CHAR(10)
 + 'UPDATE '+S.SchemaName + '.' + S.TableName +' SET '
 + STUFF(Query,1,1,'') 
 + 'WHERE ' + S.ColumnName + '=@'+S.ColumnName
 + CHAR(10)
 + 'END'
 AS Query
 FROM SysTableCTE S
 CROSS JOIN concatParamCTE
 CROSS JOIN concatUpdate
 WHERE RowNumber = 1
)


SELECT * FROM [dbo].[GenerateUpdate]('DimRestaurant')

Cela me permet d’auto générer le code de création de ma procédure d’update, et répond à mes habitudes personnelles (Id de la dimension en première colonne, nommage de la procédure …)