0

I'm attempting to come up with a RegEx pattern to identify the string of parameters in a stored procedure definition. It's rather obtuse, considering all the various possibilities, but here's what I have so far (with global flags set for case insensitivity and single-line mode):

(?:create proc.*?)((?:[@]\w+\s+[A-Za-z0-9_()]+(?:\s*=\s*\S+)?(?:\s*,\s*)?)+)(?:.*?with\s+(?:native_compilation|schemabinding|execute\s+as\s+\S+))?(?:[\s\)]*?as)

I've set up several unit tests with a variety of stored proc definitions - with or without parameters, with or without defaults, etc. - and it seems to work in all cases except for one (that I've found so far):

CREATE PROCEDURE [dbo].[sp_creatediagram]
(
    @diagramname    sysname,
    @owner_id       int = null,     
    @version        int,
    @definition     varbinary(max)
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
    set nocount on
    ...

Obviously I'd expect the first capture group to capture all four parameters...

@diagramname    sysname,
@owner_id       int = null,     
@version        int,
@definition     varbinary(max)

...but for some reason, the RegEx search halts after the second parameter - notably the one that includes a default assignment - and doesn't proceed to capture the remaining two parameters. The first capture group ends up looking like this:

@diagramname    sysname,
@owner_id       int = null,

I won't be the least bit surprised to learn that I'm grossly overcomplicating this, but I do feel like I'm really close. I imagine there must be something about the way the RegEx engine works that I'm not quite understanding. Any help is hugely appreciated. Thanks very much in advance.

Dumas.DED
  • 466
  • 5
  • 17
  • 2
    Where is this definition coming from? If it is from the database itself there are far easier ways, both in sql and c#. – Crowcoder May 19 '20 at 16:54
  • 1
    Why parse the definition? Doesn't `sys.parameters` have the information you want? – Gordon Linoff May 19 '20 at 17:02
  • 2
    @GordonLinoff - it does indeed appear that way. As I said, I had a feeling I was grossly overcomplicating this. I admittedly didn't even know about `sys.parameters` until you pointed it out. You've been enormously helpful already. – Dumas.DED May 19 '20 at 17:17
  • You have [here](https://stackoverflow.com/questions/20115881/how-to-get-stored-procedure-parameters-details) multiple answers. – Ygalbel May 19 '20 at 17:33
  • The only thing I'm still tripping up on here is determining what if any default value is associated with each parameter - apparently this isn't represented by the `has_default_value` and `default_value` columns in `sys.parameters` for t-sql objects. As far as I can tell I'll still need to do some kind of RegEx on the stored proc definition in order to get these, but that still seems simpler than what I was originally doing. – Dumas.DED May 19 '20 at 18:01
  • There are already TSQL parsers available, e.g. [`TSqlParser Class`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser?view=sql-dacfx-140.3881.1). Although the learning curve to puzzle one out may be steep, it stands a chance of handling more curious bits and may be maintained as the syntax evolves. – HABO May 19 '20 at 18:47
  • I don't know if [this would be any better](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.parameter.defaultvalue?view=sql-smo-140.17283.0) but if you wanted to use C# there is a SMO library for sql server. Think of it like the API for object explorer. – Crowcoder May 19 '20 at 19:00

0 Answers0