Microsoft SQL Server 2008 R2 Unleashed- P100:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 934 CHAPTER 29 Creating and Managing User-Defined Functions Template Explorer 4 X tilt S Extended Property 3 A Full-text J Function ij CLR Function Jtj Create Inline Function New Menu j Create Inline Table-valued Function j Create Multi-statement Function New Menu Create Multi statement Table valued Functior Create Scalar Function New Menu Jj Create Scalar-valued Function j Drop Function New SQL Server Templatej ffl Index i 4 Linked Server ffl __J Login ffl _J Partition Function l 4 Partitinn Scheme T 4 Recursive Queries 4 Resource Governor 4 Restore m Rote Recently Used Templates FIGURE Creating a new function template in SSMS. Listing shows an example of a new function template. LISTING An Example of Custom Function Template -- SQL Server 2008 Unleashed Sample -- Create scalar-valued function template USE database_name sysname bigpubs2008 GO IF OBJECT_ID N schema_nm sysname dbo . func_nm sysname fn_myfunc IS NOT NULL DROP FUNCTION schema_nm sysname dbo . func_nm sysname fn_myfunc GO CREATE FUNCTION schema_nm sysname dbo . func_nm sysname fn_myfunc parameter1 parameter2 parameter3 sysname sysname sysname @param1 @param2 @param3 RETURNS return_value_datatype int WITH EXECUTE AS CALLER AS -- place the body of the function here parameter1_datatype int parameter2_datatype int parameter3_datatype int Download from Creating and Managing User-Defined Functions 935 BEGIN DECLARE variable1 sysname @var1 variable1_datatype int variable2 sysname @var2 variable2_datatype int select variable1 sysname @var1 isnull parameter1 sysname @param1 T-SQL_Body RETURN variable1 sysname @var1 END GO After you define a custom function template you can use it as you do the built-in templates. You can double-click it or right-click and select Open and SSMS opens a new query window with a new function creation script based on the custom template. If you use the default values for the template parameters after the parameter substitution your CREATE FUNCTION .
