Transact-SQL Reference (Transact-SQL) Transact-SQL is central to using SQL Server.
All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application. The following is a list of the kinds of applications that can generate Transact-SQL: General office productivity applications. Applications that use a graphical user interface (GUI) to let users select the tables and columns from which they want to see data. Applications that use general language sentences to determine what data a user wants to see. To view the Transact-SQL reference topics To view the list of topics in the Transact-SQL reference section of SQL Server Books Online, follow these steps: The Curse and Blessings of Dynamic SQL. An SQL text by Erland Sommarskog, SQL Server MVP.
Latest revision: 2015-04-14. An earlier version of this article is also available in German. Translations provided by SQL Server MVP Frank Kalis. Introduction If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do: SELECT * FROM @tablename SELECT @colname FROM tbl SELECT * FROM tbl WHERE x IN (@list) For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it.
In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. The article covers all versions of SQL Server from SQL 6.5 to SQL 2008, with emphasis on SQL 2000 and later versions. Contents: Note: many of the code samples in this text works against the pubs and Northwind databases that ship with SQL 2000 and SQL 7, but not with SQL 2005 and later. Accessing Data from an Application. SUBSTRING (Transact-SQL) SUBSTRING ( expression ,start , length ) expression start Is an integer or bigint expression that specifies where the returned characters start.
If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. Length Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. Returns character data if expression is one of the supported character data types. The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.
The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647. Supplementary Characters (Surrogate Pairs) A. The following example shows how to return only a part of a character string. NULLIF (Transact-SQL) SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse NULLIF ( expression , expression ) -- Azure SQL Data Warehouse and Parallel Data Warehouse NULLIF (expression ,expression ) Returns the same type as the first expression.
NULLIF returns the first expression if the two expressions are not equal. NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL. We recommend that you not use time-dependent functions, such as RAND(), within a NULLIF function. The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year).
Here is the result set. CASE (Transact-SQL) SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats: The simple CASE expression compares an expression to a set of simple expressions to determine the result.The searched CASE expression evaluates a set of Boolean expressions to determine the result. Both formats support an optional ELSE argument. CASE can be used in any statement or clause that allows a valid expression.
Transact-SQL Syntax Conventions. CAST and CONVERT (Transact-SQL) Remarks Date and Time Styles When expression is a date or time data type, style can be one of the values shown in the following table.
Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm. 1 These style values return nondeterministic results. 2 The default values (style0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy). 3 Input when you convert to datetime; output when you convert to character data. 4 Designed for XML use. 5 Hijri is a calendar system with several variations.
Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. 6 Only supported when casting from character data to datetime or smalldatetime. 7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Float and real Styles money and smallmoney Styles xml Styles Binary Styles. CHARINDEX (Transact-SQL) CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) expressionToFind expressionToSearch Is a character expression to be searched. start_location Is an integer or bigint expression at which the search starts.
Bigint if expressionToSearch is of the varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, int. If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL. If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0. CHARINDEX performs comparisons based on the collation of the input. The starting position returned is 1-based, not 0-based. 0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.