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