Chapter - Stored Procedures and User-Defined Functions

Procedural Extensions, IF & WHILE Statements – SQL Server 2012

The preceding chapters introduced Transact-SQL statements that belong to the data definition language and the data manipulation language. Most of these statements can be grouped together to build a batch. As previously mentioned, a batch is a sequence of Transact-SQL statements and procedural extensions that are sent to the database system for execution together. The […]

Exception Handling with TRY, CATCH, and THROW – SQL Server 2012

Versions of SQL Server previous to SQL Server 2005 required error handling code after every Transact-SQL statement that might produce an error. (You can handle errors using the @@error global variable. Example 13.1 shows the use of this variable.) Starting with SQL Server 2005, you can capture and handle exceptions using two statements, TRY and […]

Stored Procedures , Creating and Executing – SQL Server 2012

A stored procedure is a special kind of batch written in Transact-SQL, using the SQL language and its procedural extensions. The main difference between a batch and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are saved on the server side to improve the performance […]

The EXECUTE Statement with RESULT SETS Clause – SQL Server 2012

SQL Server 2012 introduces the WITH RESULT SETS clause for the EXECUTE statement. Using this clause, you can change conditionally the form of the result set of a stored procedure. The following two examples help to explain this clause. Example 8.10 is an introductory example that shows how the output looks when the WITH RESULT […]

Stored Procedures & Common Language Runtime – SQL Server 2012

SQL Server supports the Common Language Runtime (CLR), which allows you to develop different database objects (stored procedures, user-defined functions, triggers, user-defined aggregates, and user-defined types) using C# and Visual Basic. CLR also allows you to execute these database objects using the common run-time system. Note – You enable and disable the use of CLR […]

Creation and Execution of User-Defined Functions – SQL Server 2012

User-Defined Functions are created with the CREATE FUNCTION statement, which has the following syntax:

CREATE FUNCTION [schema_name.]function_name
	[({@param } type [= default]) {,...}
	RETURNS {scalar_type | [@variable] TABLE}
	[WITH {ENCRYPTION | SCHEMABINDING}
	[AS] {block | RETURN (select_statement)}

CREATE FUNCTION [schema_name.]function_name

[({@param } type [= default]) {,...}

RETURNS {scalar_type | [@variable] TABLE}

[WITH {ENCRYPTION | SCHEMABINDING}

[AS] {block | RETURN (select_statement)}

schema_name is the name of the schema to which the ownership of the created UDF is assigned. function_name is the name of the new function. @param is an input parameter, while type specifies its data type. Parameters are values passed […]

Changing the Structure of UDFs, UDFs and CLR – SQL Server 2012

Changing the Structure of User-Defined Functions The Transact-SQL language also supports the ALTER FUNCTION statement, which modifies the structure of a User-Defined Function. This statement is usually used to remove the schema binding. All options of the ALTER FUNCTION statement correspond to the options with the same name in the CREATE FUNCTION statement. A UDF […]