Stored Procedures and User-Defined Functions

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 … [Read more...] about Procedural Extensions, IF & WHILE Statements

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.) … [Read more...] about Exception Handling with TRY, CATCH, and THROW

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, … [Read more...] about Stored Procedures , Creating and Executing

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 … [Read more...] about The EXECUTE Statement with RESULT SETS Clause

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 … [Read more...] about Stored Procedures & Common Language Runtime

User-Defined Functions are created with the CREATE FUNCTION statement, which has the following syntax: 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. … [Read more...] about Creation and Execution of User-Defined Functions

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 … [Read more...] about Changing the Structure of UDFs, UDFs and CLR