Server Interaction  «Prev  Next»

Lesson 4Functions, part 2
ObjectiveUnderstand how SQL-Server functions can be used

Transact-SQL Function Statements

The following series of images presents some common ways that you can use functions in your Transact-SQL statements:

SUBSTRING returns a specified number of characters with a string. The statement above will return all columns from the clients table, but only where the first character in the Client Type columns is 'P'
1) SUBSTRING returns a specified number of characters with a string. The statement above will return all columns from the clients table, but only where the first character in the Client Type columns is 'P'
UPPER converts a string to upper-case. This function is especially useful when you do not know the case of the text. The statement above updates every row in the ClientName columns of the clients table. It takes the current value in the ClientName column for ever row, converts it to upper case, and writes it back to the table.
2) UPPER converts a string to upper-case. This function is especially useful when you do not know the case of the text. The statement above updates every row in the ClientName columns of the clients table. It takes the current value in the ClientName column for ever row, converts it to upper case, and writes it back to the table.
@@FETCH_STATUS returns the status of the last cursor fetch operation. The statement is used in conjunction with a cursor. The WHILE loop continues until there are no more records, and thereby causing the @@FETCH_STATUS function to return a value other than 0. Inside the loop, the value of @val1 is returned back to the calling program.
3) @@FETCH_STATUS returns the status of the last cursor fetch operation. The statement is used in conjunction with a cursor. The WHILE loop continues until there are no more records, and thereby causing the @@FETCH_STATUS function to return a value other than 0. Inside the loop, the value of @val1 is returned back to the calling program.
@@SPID returns the current process ID. This function is especially useful when you are troubleshooting locking problems. The statement above returns the integer value of the identifier for the current process that executed the statement.
4) @@SPID returns the current process ID. This function is especially useful when you are troubleshooting locking problems. The statement above returns the integer value of the identifier for the current process that executed the statement.
@@ROWCOUNT returns the number of records affected by the last query. The statement above first updates the clients table, then set the ClientType column to be equal to a value of P when the client identifier equals 103. If this statement actually updated records in the table, the value of @@ROWCOUNT will be updated and returned by SQL Server as being a value greater than 0.
5) @@ROWCOUNT returns the number of records affected by the last query. The statement above first updates the clients table, then set the ClientType column to be equal to a value of P when the client identifier equals 103. If this statement actually updated records in the table, the value of @@ROWCOUNT will be updated and returned by SQL Server as being a value greater than 0.

  1. SUBSTRING returns a specified number of characters with a string. The statement above will return all columns from the clients table, but only where the first character in the Client Type columns is 'P'
  2. UPPER converts a string to upper-case. This function is especially useful when you do not know the case of the text. The statement above updates every row in the ClientName columns of the clients table. It takes the current value in the ClientName column for ever row, converts it to upper case, and writes it back to the table.
  3. @@FETCH_STATUS returns the status of the last cursor fetch operation. The statement is used in conjunction with a cursor. The WHILE loop continues until there are no more records, and thereby causing the @@FETCH_STATUS function to return a value other than 0. Inside the loop, the value of @val1 is returned back to the calling program.
  4. @@SPID returns the current process ID. This function is especially useful when you are troubleshooting locking problems. The statement above returns the integer value of the identifier for the current process that executed the statement.
  5. @@ROWCOUNT returns the number of records affected by the last query. The statement above first updates the clients table, then set the ClientType column to be equal to a value of P when the client identifier equals 103. If this statement actually updated records in the table, the value of @@ROWCOUNT will be updated and returned by SQL Server as being a value greater than 0.
Date Time Functions
UPPER converts a string to uppercase. This function is especially useful when you do not know the case of the text.
The statement above updates every row in the ClientName column of the clients table. It takes the current value in the ClientName column for every row, converts it to uppercase, and writes it back to the table.

Identifying functions

If you use the Query Analyzer or Enterprise Manager to execute or view your queries, each of the built-in functions will be pink. This can greatly help in writing and identifying functions within queries. The following figure illustrates this. Notice that the CAST function is pink.

Pink shows built-in functions
Pink shows built-in functions

In the next lesson, you will learn how to handle errors when they occur in your Transact-SQL code.