Transact-SQL

Links

• Sometimes abbreviated T-SQL, Transact-SQL is Microsoft’s and Sybase’s proprietary extension to the SQL language.

Quick Reference

• Transact-SQL Reference:

Cursors

Sample script:

../../misc/howto/microsoft/sort_out_duplicate_address.sql

DECLARE @currentDate DATETIME
DECLARE dateRangeCursor CURSOR LOCAL FOR
    SELECT calendar.cal_date
        FROM getCalendarTable('2008-08-20', '2008-08-26') calendar
        ORDER BY calendar.cal_date

OPEN dateRangeCursor
FETCH NEXT FROM dateRangeCursor INTO @currentDate
WHILE @@Fetch_status = 0
BEGIN
    PRINT @currentDate
    FETCH NEXT FROM dateRangeCursor INTO @currentDate
END
CLOSE dateRangeCursor
DEALLOCATE dateRangeCursor

Note: For more details on getCalendarTable see Code Snippets.

Database

Multiple Databases

Run The Same SQL Command Against All SQL Server Databases

DECLARE @command varchar(1000)
SET @command = '
USE ?
IF EXISTS (SELECT [name] FROM sysobjects WITH(NOLOCK) WHERE xtype=''u'' AND [name] = ''property'')
BEGIN
  SELECT ''?'', [value] FROM [property] WITH(NOLOCK) WHERE [name] = ''delivery_listener_port''
END
'
EXEC sp_MSforeachdb @command

Data Definition

See data definition, Transact-SQL - Data Definition.

Extensions

GO	Signals the end of a batch of Transact-SQL statements
USE {database}	Changes the database context to the specified database (or snapshot).

Delete

TRUNCATE TABLE

TRUNCATE TABLE name

Removes all rows from a table without logging the individual row deletes.

Exec

Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly:

DECLARE @Col2 SMALLINT, @Col1 INT
SELECT @Col2 = 3,@Col1 = 4

DECLARE @SQL NVARCHAR(1000)
SELECT @SQL = 'SELECT * FROM dbo.test
    WHERE Col2 = @InnerCol2 AND Col1 = @InnerCol1'

DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@InnerCol2 SMALLINT ,@InnerCol1 INT'

EXEC SP_EXECUTESQL @SQL, @ParmDefinition,
    @InnerCol2=@Col2,
    @InnerCol1=@Col1

Output parameter:

DECLARE @TableName VARCHAR(100),
    @TableCount INT,
    @SQL NVARCHAR(100)

SELECT @TableName = 'Test'
SELECT @SQL = N'SELECT @InnerTableCount = COUNT(*) FROM ' + @TableName

EXEC SP_EXECUTESQL @SQL, N'@InnerTableCount INT OUTPUT', @TableCount OUTPUT
SELECT @TableCount

Functions

SQLTeam, User Defined Functions

Define

See examples in Code Snippets.

Execute

To call this function:

SELECT dbo.formatPostcode('AB20 3CD')

CHARINDEX

Returns the starting position of the specified expression in a character string:

CHARINDEX(expression1, expression2 [, start_location])

• expression1 the sequence of characters to be found.

• expression2 usually a column that is searched for the specified sequence.

• start_location is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

• Useful with SUBSTRING (see below).

Sample

SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'

CHECKSUM

CHECKSUM

DECLARE @checksumFromDateMessageId INT
SET @checksumFromDateMessageId = CHECKSUM(@fromAddress, @receivedDatetime, @messageId)

CONVERT

CONVERT

CONVERT (data_type [(length)], expression[,style])

• expression Is any valid expression.

• data_type Is the target data type.

• length Is an optional integer that specifies the length of the target data type. The default value is 30.

• style can be used to format dates etc…

Sample

convert(varchar(50), description)

DATEADD

DATEADD (T-SQL)

Yesterday…

DATEADD(day, -1, GETDATE())

DATEDIFF

Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate:

DATEDIFF(datepart, startdate, enddate)
SELECT DATEDIFF(hour, '2005-12-31 15:00:00', '2005-12-31 22:30:00')

DATENAME

DATENAME

This example extracts the month name from the date returned by GETDATE:

SELECT DATENAME(month, GETDATE()) AS 'Month Name'
SELECT DATENAME(weekday, GETDATE()) AS 'Week Day'

DATEPART

DATEPART

SELECT DATEPART([day], GETDATE()) AS 'Day'
SELECT DATEPART([month], GETDATE()) AS 'Month'
SELECT DATEPART([hour], GETDATE()) AS 'Hour'
SELECT DATEPART([year], GETDATE()) AS 'Year'
SELECT DATEPART([weekday], GETDATE()) -- e.g. 4 (or 5) for Thursday

GETDATE

Current date/time (now):

GETDATE()

IS NULL

IF @ingredient1 IS NULL
BEGIN

Note: Also worth reviewing Code Snippets, NULL.

ISNULL

Note: To check if a value is NULL or not: IF @promo_terms IS NOT NULL.

Replaces NULL with the specified replacement value.

This example substitutes the value $10.00 for all NULL entries in the price column:

ISNULL(price, $10.00)

Note:

• The value of check_expression (ISNULL(check_expression, replacement_value)) is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

LOWER

LOWER(@firstName)

REPLACE

REPLACE

Replaces all occurrences of a specified string value with another string value.

REPLACE(string_expression1, string_expression2, string_expression3)

• string_expression1. Is the string expression to be searched.

• string_expression2. Is the substring to be found.

• string_expression3. Is the replacement string.

• Returns NULL if any one of the arguments is NULL.

Sample

Remove spaces from a string:

REPLACE(body, ' ', '')

SUBSTRING

Return part of a character, binary, text, or image expression:

SUBSTRING

SUBSTRING(expression, start, length)

• expression Is a character string or an expression that includes a column.

• start Is an integer that specifies where the substring starts.

• length Is a positive integer that specifies how many characters of the expression will be returned.

• also see CHARINDEX (above).

Sample

SUBSTRING(CAST(text as NVARCHAR(MAX)), 4, LEN(CAST(text as NVARCHAR(MAX))))

UPPER

UPPER(@gonzoBody)

Select

Correlated Subquery

Using a Correlated Subquery:

SELECT CustNum, CompanyName, SortName,
  (
    SELECT MAX(CCL.ContactDate)
      FROM tblCustContactLog CCL
      WHERE (CCL.CustNum = Cust.CustNum)
      AND (CCL.TypeDesc = 'Action')
  ) AS LastContactDate, Manager, Phone1, PhoneT1, PhoneD1
  FROM tblCustomer AS Cust
  WHERE (Cust.Active = 1) AND
    (Cust.TestCustomer = 0)
  ORDER BY LastContactDate

INTO

The SELECT INTO Statement…

The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.

The following example makes a backup copy of the Persons table:

SELECT * INTO temp_backup_persons_pjk_2008_04_10 FROM persons

Database Copy

To copy a table to another database (probably on the same server):

SELECT * INTO livedata.dbo.temp_pjk FROM demodata.dbo.temp_pjk

Note: This query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc…

Multi Value

To assign multiple values to variables:

SELECT @latitude = latitude, @longitude = longitude
    FROM addresses.postcode_district WITH(NOLOCK)
    WHERE areacode = @lookup

Rank and Row Number

-- DROP TABLE [dbo].[learn_sql_scoreboard]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[learn_sql_scoreboard](
  [id] [INT] IDENTITY(1,1) NOT NULL,
  [name] [VARCHAR](100) NOT NULL,
  [score] [INT] NOT NULL,
  CONSTRAINT [PK_learn_sql_scoreboard] PRIMARY KEY CLUSTERED
  (
    [id] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT learn_sql_scoreboard WITH(UPDLOCK ROWLOCK) ([name], score)
VALUES('Martin', 2)
INSERT learn_sql_scoreboard WITH(UPDLOCK ROWLOCK) ([name], score)
VALUES('Peter', 10)
INSERT learn_sql_scoreboard WITH(UPDLOCK ROWLOCK) ([name], score)
VALUES('Alison', 1)
INSERT learn_sql_scoreboard WITH(UPDLOCK ROWLOCK) ([name], score)
VALUES('Barry', 2)
GO
SELECT * FROM learn_sql_scoreboard WITH(NOLOCK)
-- id name  score
-- 1  Martin    2
-- 2  Peter    10
-- 3  Alison    1
-- 4  Barry     2
GO
SELECT *,
  RANK() OVER (ORDER BY score) AS 'Rank',
  ROW_NUMBER() OVER (ORDER BY score) AS 'RowNumber'
  FROM learn_sql_scoreboard WITH(NOLOCK)
  -- WHERE score > 1
  ORDER BY id
-- id name  score   Rank  RowNumber
-- 1  Martin    2   2     3
-- 2  Peter    10   4     4
-- 3  Alison    1   1     1
-- 4  Barry     2   2     2

Wildcard

% will match any string of zero or more characters. To find all book titles with the word computer anywhere in the book title:

WHERE title LIKE '%computer%'

Security

SQL Injection

Look at the REPLACE and QUOTENAME functions…

Stored Procedures

Create

Sample (showing more than one parameter):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sample_procedure_name]
    @code_id INT, @user_id INT
AS
BEGIN
    SET NOCOUNT ON
    SELECT * FROM sample_table WHERE code_id = @code_id AND user_id = @user_id;
END

Escape

SET @sql = @sql + N'WHERE first_name = ''' + @first_name + ''' AND surname = ''' + @surname + ''''

Execute

EXEC sample_procedure_name @code_id = 'A1'

Parameters

String parameters are declared as follows:

@name VARCHAR(50)

Default

@voucher VARCHAR(MAX) = ''

Output

Output parameters are used in a stored procedure like this:

CREATE PROC allocate_code
  @user_account_id int,
  @new_code varchar(7) OUTPUT
AS

…to access the result of this stored procedure:

DECLARE @result VARCHAR(50)
EXEC allocate_code @user_account_id = 1, @new_code = @result OUTPUT
SELECT @result

…or this (which I can’t get working):

DECLARE @result VARCHAR(50)
EXEC @result = EXEC allocate_code @user_account_id = 1
SELECT 'Return Value' = @result

Unicode

Unicode character constants are prefixed with “N”:

insert Contacts(ContactId, Name, Note, Resume)
values (N'CO-92-81', N'Tom Jones', N'Tom@trigonblue.com', N'N/a'

Result

To get the result of a stored procedure:

DECLARE @countCoke INT
DECLARE @countTable TABLE
(
  total INT
)
INSERT @countTable(total)
EXEC remove_brand_name_flavours coke

SET @countCoke = (SELECT total FROM @countTable)
PRINT @countCoke

Note: Yes, this is hard to believe… for more information see:

• Return Recordsets from Dynamic Queries called by EXEC

• Getting Creative with EXEC and EXEC()

Union

SELECT Name, City FROM Suppliers
    WHERE Country = 'Mexico'
    UNION
        SELECT Name, City FROM Customers
            WHERE Country = 'Mexico'