\nAlthough 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.\n<\/p>\n
\nYou can't have defaults on parameters in .NET, but you can specify them in the CREATE PROCEDURE DDL, for example:\n<\/p>\n
\nCREATE PROCEDURE addwithdefaults (@x int, @z int out, @y int = 5)
\nAS EXTERNAL NAME sampleasm.StoredProcedures.AddWithDefaults\n<\/p>\n
\nThese 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.\n<\/p>\n
\nBut, 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.\n<\/p>\n
\nI 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,29,38],"tags":[],"class_list":["post-674","post","type-post","status-publish","format-standard","hentry","category-smo","category-sql-server-2008","category-sqlclr"],"yoast_head":"\n