Default values in mssql

[stev...@gmail.com]

I see the comment in the Parameter interface about default values only being supported in 11g.  I know that mssql's metadata doesn't contain the default values (stupidly) but it wouldn't be that hard to parse the definition from sql_modules to get the default value? Probably don't need a full tsql grammer or this.  I realize its hacky...but we have thousands of stored procedures that take default values and its really unfortunate that we lose the feature entirely.

In the meantime - I'm not entirely sure why AbstractRoutine#addInParameter defaults the values to null.  Maybe it could default to something that you could detect later and chose to just omit that parameter so that the database would use its default?

[Lukas Eder]

Hi Steve,

Thanks for your enquiry. There's no particular reason why this should only be supported for Oracle. It's just that we haven't had any specific requests for other databases yet.

Indeed, unfortunately, the sys.parameters.has_default_value column is only populated for CLR procedures, not for Transact-SQL procedures, as it seems:

- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d967730-2a29-412b-852a-7e5c2cec46f5/hasdefaultvalue-in-sql-server-2005-sysparameters-isnt-set-to-true-if-default-value-is-null-how

Of course, it's a good idea to add another vote to this feature here. Maybe we'll see it in a future SQL Server version:

- https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=234143

As far as parsing goes, we already do some parsing in other databases where the dictionary views don't suffice, so I suspect that we can live with this hack. I've registered a feature request for this:

https://github.com/jOOQ/jOOQ/issues/3686

If you don't mind, I'd like to go through this together with you, as you probably have a couple of syntax corner cases in your database that we won't think of. The procedure definition syntax is documented here:

- http://msdn.microsoft.com/en-us/library/ms186755.aspx (CREATE FUNCTION)

- http://msdn.microsoft.com/en-us/library/ms187926.aspx (CREATE PROCEDURE)

So, we have something like this syntax:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS ...

And...

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS

A couple of remarks:

- Even if the syntax for procedures omits parentheses to wrap parameters, they are allowed (and thus optional)

- Parameters can be clearly identified by their leading @ signs

- Defaulted parameters can be identified by an equal sign

- The last parameter is followed by:

    o a closing parenthesis in functions followed by the RETURNS keyword. Data types can also have parentheses, though

    o any of these keywords: WITH, FOR, AS possibly preceded by a closing parenthesis.

    o since "default" can be a VARCHAR value, we must probably parse the values to ensure that we don't capture closing parenthesis or terminating keywords that are contained inside of those default values

Do you see any edge cases that fall outside of the remarks mentioned above?

2014-10-13 17:19 GMT+02:00 <stev...@gmail.com>:

In the meantime - I'm not entirely sure why AbstractRoutine#addInParameter defaults the values to null.  Maybe it could default to something that you could detect later and chose to just omit that parameter so that the database would use its default?

The problem here is that JDBC's support for named parameters is not very sophisticated. While it is possible to specify named parameters on a CallableStatement, from how I see it, it's not possible to declare such named parameters in the SQL string, neither using the JDBC escape syntax { call my_procedure(?, ?, ?) }, nor if generating Transact-SQL.

With indexed parameters, defaults are not really possible, because all bind variables need to be bound to a value, and unfortunately there is no "DEFAULT" binding as far as I can tell...?

Defaulting to using named parameters with generated Transact-SQL (e.g. "exec my_procedure @non_default = 1") might not be a good idea either. We've had customers in the past who were relying on the fact that indexed parameters were used, i.e. they were free to tweak parameter names...

[Lukas Eder]

Finally, jOOQ does have a parser (since jOOQ 3.9) and it is now sophisticated enough to easily handle parsing T-SQL procedure / function signatures. This means that the feature will be implemented in jOOQ 3.11:

https://github.com/jOOQ/jOOQ/issues/3686

Cheers,

Lukas