vendredi 23 octobre 2015

Regex to replace stored procedure params with declarations

Quite often I have to rewrite stored procedure because I don't have execution rights to a "normal" code - meaning rewriting stored procedure params to declarations + SETs.

For example:

@pParam  VARCHAR(20),
@pPlant  VARCHAR(4) = NULL,
@pDateTimeFrom  VARCHAR(30) = NULL,
@pDateTimeTo  VARCHAR(30) = NULL,
@pLanguage  VARCHAR(2) = 'EN',
@pClass  NVARCHAR(MAX) = NULL,
@pFilterCompleteDate  BIT = NULL,
@user VARCHAR(50) = NULL,
@updateKey VARCHAR(50) = NULL,
@seqnoKey INT = NULL,
@comment VARCHAR(1000) = NULL,
@testID INT = NULL,
@info NVARCHAR(MAX) = NULL

And with regex find and replace I want it in this format:

DECLARE @pParam VARCHAR(20) 
SET @pParam = 
DECLARE @pPlant VARCHAR(4) 
SET @pPlant = NULL
DECLARE @pDateTimeFrom VARCHAR(30) 
SET @pDateTimeFrom = NULL
DECLARE @pDateTimeTo VARCHAR(30) 
SET @pDateTimeTo = NULL
DECLARE @pLanguage VARCHAR(2) 
SET @pLanguage = 'EN'
DECLARE @pClass NVARCHAR(MAX) 
SET @pClass = NULL
DECLARE @pFilterCompleteDate BIT 
SET @pFilterCompleteDate = NULL
DECLARE @user VARCHAR(50) 
SET @user = NULL
DECLARE @updateKey VARCHAR(50) 
SET @updateKey = NULL
DECLARE @seqnoKey INT 
SET @seqnoKey = NULL
DECLARE @comment VARCHAR(1000) 
SET @comment = NULL
DECLARE @testID INT 
SET @testID = NULL
DECLARE @info NVARCHAR(MAX) 
SET @info = NULL

I came up with this regex

Find:

(@.+?)\b\s+\b(.*?)(\s=\s(.*),?|,)

Replace:

DECLARE \1 \2 \r\nSET \1 = \4

But the 4th capture group includes the , at the end of the string. When I included it in the regex, it didn't get the first line without value and last line without comma.

Could you help me find a regex which would correctly find all declarations (no value, value, no comma). Thanks!

Aucun commentaire:

Enregistrer un commentaire