Note on <Beginning Microsoft SQL Server 2012 Programming> - 04

Chapter 11: Writing Scripts and Batches

Scripts basics

Scripts are usually treated as a unit. That is, you are normally executing the entire script or nothing at all.

Select Database Context

The USE statement sets the current database.

Declare Variables

DECLARE @<variable name> <variable type>[= <value>][,
	@<variable name> <variable type>[= <value>][,
	@<variable name> <variable type>[= <value>]]]

You can declare just one variable at a time, or several.

A scalar variable is one that holds a single, atomic value, like an integer or a string. SQL Server also allows you to declare table variables.

DECLARE @<variable name> TABLE (
	<column spec> [, . . .]
)

Set Variables

There are three ways to set the value in a variable:

• initialize it in the DECLARE statement(not work in SQL Server 2005: DECLARE @Counter INT = 0;)
• use a SELECT statement
• use a SET statement

SELECT statement can do a couple more things:

1. SELECT can assign a value from a column in the SELECT statement
2. SELECT can assign values to many variables in the same statement

With a SET, you cannot assign a value to a variable from a query — you have to separate the query from the SET. For example:

USE AdventureWorks;
DECLARE @Test money;
SET @Test = (SELECT MAX(UnitPrice) FROM Sales.SalesOrderDetail);
SELECT @Test;

What the author said:

Use SET when you are performing a simple assignment of a variable — where your value isalready known in the form of an explicit value or some other variable. Use SELECT when you are basing the assignment of your variable on a query.

Control of flow

There are:

IF ... ELSE IF ... ELSE GOTO
BEGIN ... END
CASE WHEN THEN ELSE END	There is more than one way to write a CASE statement: Simple case: to use an input expression that will be compared with the value used in each WHEN clause. CASE <input expression> WHEN <when expression> THEN <result expression> [...n] [ELSE <result expression>] END Because you have an ELSE clause, any value that doesn't match one of the previous values is assigned whatever you've put in your ELSE. If you leave the ELSE out, any such value is given a NULL. Searched case: to provide an expression with each WHEN clause that will evaluate to TRUE/FALSE. CASE WHEN <Boolean expression> THEN <result expression> [...n] [ELSE <result expression>] END There is no input expression (remember that's the part between the CASE and the first WHEN). The WHEN expression must evaluate to a Boolean value (whereas in the simple CASE examples you’ve just looked at, you used values such as 1, 3, and ProductID + 1).
WHILE [BREAK] |[CONTINUE]
WAITFOR DELAY
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH	ERROR LEVEL NATURE DESCRIPTION 1–10 Informational only. This includes things like context changes such as settings being adjusted or NULL values found while calculating aggregates. These will not trigger a CATCH block, so if you need to test for this level of error, you’ll need to do so manually by checking @@ERROR. 11–19 Relatively severe errors. These are errors that can be handled by your code (foreign key violations, as an example). Some of these can be severe enough that you are unlikely to want to continue processing (such as a memory exceeded error), but at least you can trap them and exit gracefully. 20–25 Very severe. These are generally system-level errors. Your server-side code will never know this kind of error happened, as the script and connection will be terminated immediately. FUNCTION RETURNS ERROR_NUMBER() The actual error number. If this is a system error, there will be an entry in the sysmessages table (use sys.messages to look it up) that matches to that error and contains some of the information you'll get from the other error-related functions. ERROR_SEVERITY() This equates to what is sometimes called "error level" in other parts of this book and Books Online. My apologies for the inconsistency — I'm guilty of perpetuating something that Microsoft started doing a few versions ago. ERROR_STATE() I use this as something of a place mark. This is always 1 for system errors. When I discuss error handling in more depth in the next chapter, you'll see how to raise your own errors. At that point, you can use state to indicate things like at what point in your stored procedure, function, or trigger the error occurred (this helps with situations where a given error can be handled in any one of many places). ERROR_PROCEDURE() We did not use this in the preceding example because it is only relevant to stored procedures, functions, and triggers. This supplies the name of the procedure that caused the error — very handy if your procedures are nested at all — because the procedure that causes the error may not be the one to actually handle that error. ERROR_LINE() Just what it says — the line number of the error. ERROR_MESSAGE() The text that goes with the message. For system messages, this is the same as what you'll see if you select the message from the sys.messages function. For user-defined errors, it's the text supplied to the RAISERROR function.

System Functions

VARIABLE	PURPOSE	COMMENTS
@@DATEFIRST	Returns what is currently set as the first day of the week (say, Sunday versus Monday).	Is a system-wide setting — if someone changes the setting, you may not get the result you expect.
@@ERROR	Returns the error number of the last T-SQL statement executed on the current connection. Returns 0 if no error.	Is reset with each new statement. If you need the value preserved, move it to a local variable immediately after the execution of the statement for which you want to preserve the error code.
@@IDENTITY	Returns the last identity value inserted as a result of the last INSERT or SELECT INTO statement in the current connection.	Is set to NULL if no identity value was generated. This is true even if the lack of an identity value was due to a failure of the statement to run. If multiple inserts are performed by just one statement, only the last identity value is returned.
IDENT_CURRENT ('table_name')	Returns the last identity value inserted for a specified table regardless of session or scope.	Nice in the sense that it doesn't get overwritten if you’re inserting into multiple tables, but can give you a value other than what you were expecting if other connections are inserting into the specific table.
@@OPTIONS	Returns information about options that have been set using the SET command.	Because you get only one value back, but can have many options set, SQL Server uses binary flags to indicate what values are set. To test whether the option you are interested is set, you must use the option value together with a bitwise operator.
@@REMSERVER	Used only in stored procedures. Returns the value of the server that called the stored procedure.	Handy when you want the sproc to behave differently depending on the remote server (often a geographic location) from which it was called. Still, in this era of .NET, I would question whether anything needing this variable might have been better written using other functionality found in .NET.
@@ROWCOUNT	One of the most used system functions. Returns the number of rows affected by the last statement.	Commonly used in non-runtime error checking. For example, if you try to DELETE a row using a WHERE clause, and no rows are affected, that implies that something unexpected happened. You can then raise an error manually.
SCOPE_IDENTITY()	Similar to @@IDENTITY, but returns the last identity inserted within the current session and scope.	Very useful for avoiding issues where a trigger or nested stored procedure has performed additional inserts that have overwritten your expected identity value. If you are trying to retrieve an identity value for an insert you've just performed, this is the way to go.
@@SERVERNAME	Returns the name of the local server that the script is running from.	Can be changed by using sp_addserver and then restarting SQL Server, but rarely required.
@@TRANCOUNT	Returns the number of active transactions — essentially the transaction nesting level — for the current connection.	A ROLLBACK TRAN statement decrements @@TRANCOUNT to 0 unless you are using save points. BEGIN TRAN increments @@TRANCOUNT by 1. COMMIT TRAN decrements @@TRANCOUNT by 1.
@@VERSION	Returns the current version of SQL Server, as well as the date, processor, and O/S architecture.	Unfortunately, this doesn't return the information into any kind of structured field arrangement, so you have to parse it if you want to use it to test for specific information. Also be sure to check out the xp_msver extended stored procedure.

SCOPE_IDENTITY()

Because SQL Server generated that value instead of you supplying it, you need to have a way to retrieve that value for use in your dependent inserts later on in the script. SCOPE_IDENTITY() gives you that automatically generated value because it was the last statement run.

Difference between @@IDENTITY and SCOPE_IDENTITY():

If an insert causes a trigger to fire and that trigger causes an insert into a different table containing another identity column, @@IDENTITY will pick up that value — it was created during the current connection. SCOPE_IDENTITY() will pick up only the insert done in the current batch scope. For most cases where the two differ, SCOPE_IDENTITY() is what's needed, and otherwise they're the same.

SEQUENCES

A sequence returns the next value(s) requested before an insert is performed. Comparing with identity column, sequence can:

• be applied across multiple tables
• be incremented by the value defined at creation
• cycle through to its minimum value once the maximum is reached

CREATE SEQUENCE [schema_name.]sequence_name
		[ <sequence_property_assignment> [ ,...n ] ]
	[ ; ]
<sequence_property_assignment>::=
{
	[ AS { built_in_integer_type | user-defined_integer_type } ]
	| START WITH <constant>
		| INCREMENT BY <constant>
		| { MINVALUE <constant> | NO MINVALUE }
		| { MAXVALUE <constant> | NO MAXVALUE }
		| { CYCLE | NO CYCLE }
		| { CACHE [<constant> ] | NO CACHE }
}

DECLARE @Seq int;
SELECT @Seq = NEXT VALUE FOR SeqColSequence;

What the author said:

The functions like @@SCOPE_IDENTITY and @@ROWCOUNT, are reset with a new value the very next statement, so, if you're doing multiple activities with them, you should move the value off to a holding variable.

Dynamic SQL

• It runs under a separate scope than the code that calls it — that is, the calling code can't reference variables inside the EXEC statement, and the EXEC can't reference variables in the calling code after they are resolved into the string for the EXEC statement. If you need to pass values between your dynamic SQL and the routine that calls it, consider using sp_executesql or temporary table. But keep in mind, any temporary table created within the scope of your EXEC statement will only live for the life of that EXEC statement.
  There is one thing that happens inside the scope of the EXEC that can be seen after the EXEC is done — system functions — so, things like @@ROWCOUNT can still be used.
• By default, it runs under the same security context as the current user — not that of the calling object (an object generally runs under the context of the object's owner, not the current user).
  When you give people the right to run a stored procedure, you imply that they also gain the right to perform the actions called for within the sproc. However, any reference made inside an EXEC statement will, by default, be run under the security context of the current user.
  Let's say I have the right to run a procedure called spNewEmployees, but I do not have rights to the Employees table. If spNewEmployees gets the values by running a simple SELECT statement, everything is fine. If, however, spNewEmployees uses an EXEC statement to execute that SELECT statement, the EXEC statement will fail because I don't have the rights to perform a SELECT on the Employees table.
• It runs under the same connection and transaction context as the calling object.
• Concatenation that requires a function call must be performed on the EXEC string prior to actually calling the EXEC statement — you can't do the concatenation of function in the same statement as the EXEC call.
• EXEC cannot be used inside a user-defined function.
• If you're not careful, EXEC can provide an attack vector for hackers, make sure you know that what’s getting concatenated and run is code you'd approve of..

Batch

• A script can contain more than one batch.
• A batch is a grouping of T-SQL statements into one logical unit. All of the statements within a batch are combined into one execution plan, so all statements are parsed together and must pass a validation of the syntax or none of the statements will execute.
• If a statement fails at parse time, nothing runs. If a statement fails at run time, all statements until the statement that generated the error have already run, and it terminates execution of the batch from the point.

When to Use Batches

Statements That Require Their Own Batch:

• CREATE DEFAULT
• CREATE PROCEDURE
• CREATE RULE
• CREATE TRIGGER
• CREATE VIEW

Codes like the below will run into error:

USE master
CREATE DATABASE Test
--GO
USE Test
CREATE TABLE TestTable
(
	col1 int,
	col2 int
)

GO Command

To separate a script into multiple batches, you make use of the GO statement. The GO statement:

• Must be on its own line (nothing other than a comment can be on the same line)
• Causes all statements since the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independently of any other batches.
• Is not a T-SQL command, but, rather, a command recognized by the various SQL Server command utilities (sqlcmd and the Query window in the Management Studio)
  

SQLCMD

Chapter 12: Stored Procedures

The only differences between using the ALTER PROC statement and using the CREATE PROC statement are:

• ALTER PROC: Expects to find an existing sproc, where CREATE doesn't.
• ALTER PROC: Retains any permissions (also often referred to as rights) that have been established for the sproc. It keeps the same object ID within system objects and allows the dependencies to be kept. For example, if procedure A calls procedure B and you drop and re-create procedure B, you no longer see the dependency between the two. If you use ALTER, it is all still there.
• ALTER PROC: Retains any dependency information on other objects that may call the sproc being altered.

Parameterizing

Parameter must start with the @ sign (just like variables do). Other than that, the rules for naming are pretty much the same as the rules for naming described in Chapter 1, except that parameter names cannot have embedded spaces.

A beginning value must be supplied when the sproc is called.

EXEC spEmployeeByName 'Dobney'

Default parameter

CREATE PROC spEmployeeByName
	@LastName NVARCHAR(50) = NULL
AS
	IF @LastName IS NOT NULL
		...
	ELSE
		...

Call it just as if it had no parameter:

EXEC spEmployeeByName

Output parameter

CREATE PROCEDURE [dbo].[uspLogError]
	@ErrorLogID [int] = 0 OUTPUT
AS
BEGIN
	...

	SET @ErrorLogID = ...

END

DECLARE @MyOutputParameter int

EXEC dbo.uspLogError @ErrorLogID = @MyOutputParameter OUTPUT;

There are several things that you should take note of between the sproc itself and the usage of it by the calling script:

• The OUTPUT keyword was required for the output parameter in the sproc declaration.
• You must use the OUTPUT keyword when you call the sproc, much as you did when you declared the sproc. This gives SQL Server advance warning about the special handling that parameter will require. Be aware, however, that forgetting to include the OUTPUT keyword won't create a runtime error, but the value for the output parameter will wind up with what it has already been — most likely a NULL value.
• The variable you assign the output result to does not have to have the same name as the internal parameter in the sproc.
• The EXEC (or EXECUTE) keyword was required because the call to the sproc wasn't the first thing in the batch. You can leave off the EXEC if the sproc call is the first thing in a batch. Personally, I recommend that you train yourself to use it regardless.

How to Use RETURN

• Return values should be used for indicating the execution status of your sproc, success or failure, and even the extent or nature of that success or failure. That is what they are really there for.
• Return value must be an integer.
• No matter where you are in your sproc, not one more line of code will execute after you have issued a RETURN statement.
• The default return value is always zero, even though you don't specify a specific return value.

Question:

What will happen if no return statement got issued?

DEALING WITH ERRORS

Errors can fall into three categories:

1. Inline errors: Errors that SQL Server knows about, but that don't create run-time errors such that your code stops running. Like violation of constraints.
2. Runtime errors: Ones stop your code from proceeding.
3. Errors that are more logical in nature and to which SQL Server is essentially oblivious.

Why Using TRY-CATCH Over @@ERROR

By checking with @@ERROR, you can only trap the inline errors, if it is run-time error which will abort the script entirely, your trapping code will never get a chance to run.

• Error trapping with @@ERROR will not squelch the SQL Server message.
• Without the TRY block, SQL Server aborts the script entirely on the particular error, so your trapping code with @@ERROR won't have a chance to run.

Differences between @@ERROR and ERROR_NUMBER()

• Both contains the error number of the last T-SQL statement executed. If the value is zero, no error occurred.
• Whereas ERROR_NUMBER() is only valid within a CATCH block (and remains the same regardless of where you are within that CATCH block), @@ERROR receives a new value with each statement you execute.

Below is an example of Sproc, to show you how to predict and handle errors proactively using the facilities that SQL provides:

CREATE PROCEDURE HumanResources.uspEmployeeHireInfo2
	@BusinessEntityID [int],
	@JobTitle [nvarchar](50),
	@HireDate [datetime],
	@RateChangeDate [datetime],
	@Rate [money],
	@PayFrequency [tinyint],
	@CurrentFlag [dbo].[Flag]
	WITH EXECUTE AS CALLER
AS
BEGIN
	SET NOCOUNT ON;
	-- Set up “constants” for error codes
	DECLARE @BUSINESS_ENTITY_ID_NOT_FOUND int = -1000,
			@DUPLICATE_RATE_CHANGE int = -2000;
	BEGIN TRY
		BEGIN TRANSACTION;
		UPDATE HumanResources.Employee
		SET JobTitle = @JobTitle,
			HireDate = @HireDate,
			CurrentFlag = @CurrentFlag
		WHERE BusinessEntityID = @BusinessEntityID;
		IF @@ROWCOUNT > 0
			-- things happened as expected
			INSERT INTO HumanResources.EmployeePayHistory
			(BusinessEntityID,
			RateChangeDate,
			Rate,
			PayFrequency)
			VALUES
			(@BusinessEntityID,
			@RateChangeDate,
			@Rate,
			@PayFrequency);
		ELSE
			-- ruh roh, the update didn't happen, so skip the insert,
			-- set the return value and exit
		BEGIN
			PRINT 'BusinessEntityID Not Found';
			ROLLBACK TRAN;
			RETURN @BUSINESS_ENTITY_ID_NOT_FOUND;
		END
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		-- Rollback any active or uncommittable transactions before
		-- inserting information in the ErrorLog
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
		EXECUTE dbo.uspLogError;
		IF ERROR_NUMBER() = 2627 -- Primary Key violation
		BEGIN
			PRINT 'Duplicate Rate Change Found';
			RETURN @DUPLICATE_RATE_CHANGE;
		END
	END CATCH;
END;

Bring Your Own Error to SQL Server's Awareness: Manually Raising Them 

RAISERROR (<message ID | message string | variable>, <severity>, <state>
[, <argument>
[,<...n>]] )
[WITH option[,...n]]

message ID/message string	If you just supply a message string, then it will be passed to the client application, or otherwise you use a message ID, SQL Server will find the message from the sys.messages in the master database, that is associated with that ID, and return it to the client application.
severity	They can range from informational (severities 1–18), to system level (19–25), and even to catastrophic (20–25). If you raise an error of severity 19 or higher (system level), the WITH LOG option must also be specified; 20 and higher automatically terminates the users' connections. GROUP DESCRIPTION 1–10 Purely informational, but returns the specific error code in the message information. 11–16 If you do not have a TRY/CATCH block set up, these terminate execution of the procedure and raise an error at the client. The state is shown to be whatever value you set it to. If you have a TRY/CATCH block defined, that handler is called rather than raising an error at the client. 17 Usually, only SQL Server should use this severity. Basically, it indicates that SQL Server has run out of resources — for example, tempdb is full — and can’t complete the request. Again, a TRY/CATCH block gets this before the client does. 18–19 Both of these are severe errors and imply that the underlying cause requires system administrator attention. With 19, the WITH LOG option is required, and the event show up in the Windows Event Log. These are the final levels at which you can trap the error with a TRY/CATCH block — after this, it goes straight to the client. 20–25 Your world has just caved in, as has the user's connection. Essentially, this is a fatal error. The connection is terminated. As with 19, you must use the WITH LOG option and a message, if applicable, shows up in the Event Log.
state	State values can be between 1 and 127. The notion is that this gives you an opportunity to send something of a place marker for where exactly the error occurred. the same error may occur at multiple places; you need an opportunity to specify where exactly the error occurred.
Error Arguments	Some predefined errors accept arguments. These allow you to make the error somewhat more dynamic by changing to the specific nature of the error. You can also format your error messages to accept arguments. Parameter Placeholders(skip) Placeholder Options(skip) Width, precision, and long/short status of a parameter: Width: Set by simply supplying an integer value for the amount of space you want to hold for the parameterized value. You can also specify a *, in which case SQL Server automatically determines the width according to the value you’ve set for precision. Precision: Determines the maximum number of digits output for numeric data. Long/Short: Set by using an h (short) or I (long) when the type of the parameter is an integer, octal, or hex value.
WITH <option>	LOG: This tells SQL Server to log the error to the SQL Server error log and the Windows Application Log. This option is required with severity levels that are 19 or higher. SETERROR: By default, a RAISERROR command doesn't set @@ERROR with the value of the error you generated. Instead, @@ERROR reflects the success or failure of your actual RAISERROR command. SETERROR overrides this and sets the value of @@ERROR to be equal to your error ID. NOWAIT: Immediately notifies the client of the error.

Passing Errors on up to the caller: Re-throwing Errors

You can THROW an error back up the chain:

THROW [ error_number, message, state] [ ; ]

THROW always uses a severity of 16, when it is called with parameter values specified. 16 means no Windows log is written by default, but current code will stop.

Differences between THROW and RAISERROR:

• With THROW, the error_number parameter need not already exist in sys.messages. Don't be lazy about that, though — make sure your errors are defined somewhere.
• RAISERROR message strings use printf-style syntax for dynamic messaging, whereas THROW does not. If you want to customize the message you THROW, you'll have to concatenate it yourself ahead of time.
• The statement before a THROW must be terminated with a semicolon (;).

To Facilitate RAISERROR & THROW, You Can Add Your Own Custom Error Messages

sp_addmessage [@msgnum =] <msg id>,
[@severity =] <severity>,
[@msgtext =] <'msg'>
[, [@lang =] <'language'>]
[, [@with_log =] [TRUE|FALSE]]
[, [@replace =] 'replace']

The actual message is added to the master database and can be viewed at any time by looking at thesys.messages system view. To get rid of the custom message, use the following:

sp_dropmessage <message number>

What the author said:

As such, I strongly recommend keeping all your custom messages stored in a script somewhere so they can easily be added into a new system.

Common Practices: Catch the errors that SQL Server is not concerned

This practice works for the 3rd type of errors. Since SQL Server sees no problem if the matched record is not found when running an UPDATE statement. But it is good practice to detect this condition yourself and trap it before the script continues, with IF-ELSE and @@ROWCOUNT to check if the UPDATE runs successfully.

What the author said:

I tend to use positive values for return codes that are informational in nature (perhaps there are multiple possible successful outcomes and I want to indicate which successful outcome occurred) and negative values for errors. If you plan to use constant error codes, do something to enforce the link between your values and the ones in your client application. Draw your values from a common spreadsheet, or create a lookup table of error values that is used by both the sprocs and the application code.

WHAT A SPROC OFFERS

• Making processes that require procedural action callable: Sprocs can call to other sprocs (called nesting). For SQL Server 2012, you can nest up to 32 levels deep.
  
• Security: Granting users the right to execute a sproc implies that they can perform any action within the sproc, provided that the action is taken within the context of the sproc. That is, if you grant users authority to execute a sproc that returns all the records in the Customers table, but not access to the actual Customers table, the users can still get data out of the Customers table, provided they do it by using the sproc. What can be really handy here is that you can give users access to modify data through the sproc, but then only give them read access to the underlying table. They can only modify data in the table provided that they do it through your sproc.
  
• Performance:  When you run the CREATE PROC command, it can make use of deferred name resolution. Deferred name resolution ignores the fact that you may have some objects that don't exist yet. This gives you the chance to create these objects later.
  At the first time it is executed, the sproc is optimized and a query plan is compiled and cached on the system. Subsequent times that you run your sproc will, unless you specify otherwise using the WITH RECOMPILE option, generally use that cached query plan rather than create a new one. This means that whenever the sproc is used it can skip much of the optimization and compilation process.

When a Good Sproc Goes Bad

If your query is dynamic in nature (the query is built up as it goes using the EXEC command), or the query is chosed conditionally with IF...ELSE statements. The sproc may be optimized for the way things ran the first time, after that first time, it may selects a different query to run, it will still use the query plan based on the first time the sproc ran. In short, the query performance is really going to suffer.

By using the WITH RECOMPILE option, you can force a new plan was created just for this run.

1. You can include the WITH RECOMPILE at runtime. This tells SQL Server to throw away the existing execution plan and create a new one — but just this once — that is, just for this time that you’ve executed the sproc using the WITH RECOMPILE option. Without further action, SQL will continue to use the new plan forevermore.
   

   EXEC spMySproc '1/1/2012'
   WITH RECOMPILE

2. Or you can choose to make things more permanent by including the WITH RECOMPILE option right within the sproc. If you do things this way, you add the WITH RECOMPILE option immediately before the AS statement in your CREATE PROC or ALTER PROC statements. If you create your sproc with this option, the sproc will recompile each time that it runs, regardless of other options chosen at runtime.
   

What the author said:

Setting users up to directly link to a production database via Access or Excel can empower your users, but they will bog down the system as well. Because they may run long queries and be oblivious to the havoc this causes your system. If you really must give users direct access, consider using mirroring, replication, or backups and restores to create a completely separate copy of the database (or just the tables they need access to) for them to use. This helps ensure you against record locks and long-running queries.

RECURSION

It can go on and on up to a limit of 32 levels of recursion. Once SQL Server gets 32 levels deep, it raises an error and ends processing.

What the author said:

Any calls into .NET assemblies count as an extra level in your recursion count, but anything you do within those assemblies does not count against the recursion limit. That it is a potential way around nesting level issues.

Chapter 13: User-Defined Functions

CREATE FUNCTION [<schema name>.]<function name>
( [ <@parameter name> [AS] [<schema name>.]<data type>
[ = <default value> [READONLY]]
[ ,...n ] ] )
RETURNS {<scalar type>|TABLE [(<table definition>)]}
[ WITH [ENCRYPTION]|[SCHEMABINDING]|
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |
[EXECUTE AS { CALLER|SELF|OWNER|<’user name’>} ]
]
[AS] { EXTERNAL NAME <external method> |
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause>|RETURN (<SELECT statement>)}
END }[;]

• You are not limited to an integer for a return value — instead,it can be of any valid SQL Server data type (including userdefined data types!), except for BLOBs, cursors, and timestamps.
  
• Unlike sprocs, the whole purpose of the return value is to serve as a meaningful piece of data.
  
• You can perform functions inline to your queries. For instance, you can include it as part of your SELECT statement.
  
• SQL doesn't resolve scalar value functions the way it does with other objects, and hencethe schema (dbo. in this case) is required.
  
• You can embed queries in them and use them as an encapsulation method for subqueries.
  
• Almost anything you can do procedurally that returns a discrete value could also be encapsulated in a UDF and used inline with your queries.
  
• It's completely legal to embed one UDF in another one.
  

There are two types of UDF, in terms of what they return:

• Those that return a scalar value
  
• Those that return a table
  

Return-Table-Value UDF

CREATE FUNCTION dbo.fnContactList()
RETURNS TABLE
AS
	RETURN (SELECT ...
	FROM ...);
GO

Indeed, they can return tables that are created using multiple statements — the only big difference when using multiple statements is that you must both name and define the metadata (much as you would for a table variable) for what you'll be returning.

CREATE FUNCTION dbo.fnGetReports
(@EmployeeID AS int)
RETURNS @Reports TABLE
(
	EmployeeID int NOT NULL,
	ManagerID int NULL
)
AS
BEGIN

There are two types of UDF, in terms of the way they work:

• deterministic
  
• non-deterministic
  

To be considered deterministic, a function has to meet four criteria:

1. The function must be schema-bound.
   
2. All other functions referred to in your function must also be deterministic.
   
3. The function cannot use an extended stored procedure.
   
4. Created WITH SCHEMABINDING option.
   

To check this out, you can make use of the OBJECTPROPERTY function:

SELECT OBJECTPROPERTY(OBJECT_ID('IsInFiscalYear'), 'IsDeterministic')