SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008

Although being able to use a GUI tool like SQL Server Management Studio is a nice feature by itself, it's unusual that fixing something in a GUI makes a feature (that hasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters.

You can't have defaults on parameters in .NET, but you can specify them in the CREATE PROCEDURE DDL, for example:

CREATE PROCEDURE addwithdefaults (@x int, @z int out, @y int = 5)
AS EXTERNAL NAME sampleasm.StoredProcedures.AddWithDefaults

These work just like defaults in T-SQL procedures; if you don't specify the parameter, the SQLCLR code receives the default value when it is called. Dandy…it's always worked this way.

But, in SQL Server 2005 SSMS (and SMO) the SQLCLR procedure was always displayed as "No Default". Even though there's perfectly good information in the SQL Server metadata about the default. Some folks were convinced that, even though default parameters worked as they should, it was dangerous to use them. Say a DBA was to move the procedure from test to production by scripting the CREATE PROCEDURE statement from SSMS (or SMO). They'd lose the default and when the procedure was defined with the generated script, code that worked in test (depended on the default value) would break in production.

I just noticed in SQL Server 2008 SSMS that defaults ARE displayed and procedures ARE scripted correctly. Great. Let a thousand SQLCLR procedures with default parameters bloom…I guess.

2 thoughts on “SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008”

  1. Great. But why does SSMS do that by parsing the procedure’s body? 🙂

    Notice that sys.parameters never informs us about default values… Both has_default_value and default_value columns do not contain information about the default value of the parameter. No matter if the parameter is of CLR or T-SQL object.

    But yes, they’ve fixed scripting for CLRs 🙂

  2. Sorry, in my previous comment I made a mistake 🙂

    Of course, I was thinking about T-SQL procedures when I wrote about parsing. With CLR objects there is no problem with metadata. The problem is only with T-SQL objects.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course