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… [Continue Reading]
Stored Procedures and User-Defined Functions
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… [Continue Reading]
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… [Continue Reading]
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… [Continue Reading]
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… [Continue Reading]
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)} schema_name is the name of the schema to which the ownership of the created UDF is assigned. function_name… [Continue Reading]
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… [Continue Reading]
