DECLARE @local_variable (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-database in Microsoft Fabric Preview

Variabelen worden gedeclareerd in de hoofdtekst van een batch of procedure met de DECLARE-instructie en worden toegewezen waarden met behulp van een SET- of SELECT-instructie. Cursorvariabelen kunnen worden gedeclareerd met deze instructie en worden gebruikt met andere cursorgerelateerde instructies. Na de declaratie worden alle variabelen geïnitialiseerd als NULL, tenzij er een waarde wordt opgegeven als onderdeel van de declaratie.

Transact-SQL syntaxis-conventies

Syntax

De volgende syntaxis is voor SQL Server en Azure SQL Database:

DECLARE
{
  { @local_variable [AS] data_type [ = value ] }
  | { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }

<table_type_definition> ::=
    TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )

<column_definition> ::=
    column_name { scalar_data_type | AS computed_column_expression }
    [ COLLATE collation_name ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
    [ ROWGUIDCOL ]
    [ <column_constraint> ]
    [ <column_index> ]

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( < index_option > [ ,...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<column_index> ::=
    INDEX index_name [ CLUSTERED | NONCLUSTERED ]
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name (column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ ,...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ ,...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<table_index> ::=
{
    {
      INDEX index_name  [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
         (column_name [ ASC | DESC ] [ ,... n ] )
    | INDEX index_name CLUSTERED COLUMNSTORE
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
    }
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}

<index_option> ::=
{
  PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
       [ ON PARTITIONS ( { partition_number_expression | <range> }
       [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ] ]
}

De volgende syntaxis is voor Azure Synapse Analytics en Parallel Data Warehouse en Microsoft Fabric:

DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]

Arguments

@ local_variable

De naam van een variabele. Namen van variabelen moeten beginnen met een @-teken. Namen van lokale variabelen moeten voldoen aan de regels voor id's.

data_type
Elk door het systeem opgegeven, common language runtime (CLR) door de gebruiker gedefinieerd tabeltype of aliasgegevenstype. Een variabele kan niet van het gegevenstype tekst, ntext of afbeelding zijn.

Zie Gegevenstypen (Transact-SQL) voor meer informatie over systeemgegevenstypen. Zie CREATE TYPE (Transact-SQL) voor meer informatie over door de gebruiker gedefinieerde CLR-typen of aliasgegevenstypen.

= value
Hiermee wordt een waarde toegewezen aan de variabele in de regel. De waarde kan een constante of een expressie zijn, maar moet overeenkomen met het type variabeledeclaratie of impliciet worden geconverteerd naar dat type. Zie Expressies (Transact-SQL)voor meer informatie.

@ cursor_variable_name

De naam van een cursorvariabele. Namen van cursorvariabelen moeten beginnen met een @-teken en voldoen aan de regels voor id's.

CURSOR
Hiermee geeft u op dat de variabele een lokale cursorvariabele is.

@ table_variable_name
De naam van een variabele van het type tabel. Variabelenamen moeten beginnen met een @-teken en voldoen aan de regels voor id's.

<table_type_definition>
Hiermee definieert u het gegevenstype van de tabel . De tabeldeclaratie bevat kolomdefinities, namen, gegevenstypen en beperkingen. De enige toegestane beperkingstypen zijn PRIMAIRE SLEUTEL, UNIEK, NULL en CHECK. Een aliasgegevenstype kan niet worden gebruikt als een gegevenstype scalaire kolom als een regel of standaarddefinitie afhankelijk is van het type.

<table_type_definition>

Een subset met informatie die wordt gebruikt voor het definiëren van een tabel in CREATE TABLE. Elementen en essentiële definities worden hier opgenomen. Zie CREATE TABLE (Transact-SQL)voor meer informatie.

n
Een tijdelijke aanduiding die aangeeft dat meerdere variabelen kunnen worden opgegeven en toegewezen waarden. Bij het declareren van tabelvariabelen moet de tabelvariabele de enige variabele zijn die wordt gedeclareerd in de instructie DECLARE.

column_name

De naam van de kolom in de tabel.

scalar_data_type
Hiermee geeft u op dat de kolom een scalair gegevenstype is.

computed_column_expression
Een expressie die de waarde van een berekende kolom definieert. Deze wordt berekend op basis van een expressie met behulp van andere kolommen in dezelfde tabel. Een berekende kolom kan bijvoorbeeld de definitiekostenAS-prijs * qty hebben. De expressie kan een niet-berekende kolomnaam, constante, ingebouwde functie, variabele of een combinatie van deze zijn die zijn verbonden door een of meer operators. De expressie kan geen subquery of een door de gebruiker gedefinieerde functie zijn. De expressie kan niet verwijzen naar een door de gebruiker gedefinieerd CLR-type.

[ COLLATE collation_name ]

Hiermee geeft u de sortering voor de kolom. collation_name kan een Windows-sorteringsnaam of een SQL-sorteringsnaam zijn en is alleen van toepassing op kolommen van het teken, varchar, tekst, nchar, nvarchar en ntextgegevenstypen . Als deze niet is opgegeven, wordt de kolom toegewezen aan de sortering van het door de gebruiker gedefinieerde gegevenstype (als de kolom van een door de gebruiker gedefinieerd gegevenstype is) of de sortering van de huidige database.

Zie COLLATE (Transact-SQL)voor meer informatie over de namen van Windows- en SQL-sorteringen.

DEFAULT

Hiermee geeft u de opgegeven waarde voor de kolom op wanneer een waarde niet expliciet wordt opgegeven tijdens een invoeging. STANDAARDdefinities kunnen worden toegepast op kolommen, behalve de kolommen die zijn gedefinieerd als tijdstempel of de definities met de eigenschap IDENTITY. STANDAARDdefinities worden verwijderd wanneer de tabel wordt verwijderd. Alleen een constante waarde, zoals een tekenreeks; een systeemfunctie, zoals een SYSTEM_USER(); of NULL kan als standaardwaarde worden gebruikt. Om compatibiliteit met eerdere versies van SQL Server te behouden, kan een beperkingsnaam worden toegewezen aan een STANDAARD.

constant_expression
Een constante, NULL of een systeemfunctie die wordt gebruikt als de standaardwaarde voor de kolom.

IDENTITY

Geeft aan dat de nieuwe kolom een identiteitskolom is. Wanneer er een nieuwe rij aan de tabel wordt toegevoegd, biedt SQL Server een unieke incrementele waarde voor de kolom. Identiteitskolommen worden vaak gebruikt met primaire sleutelbeperkingen om te fungeren als de unieke rij-id voor de tabel. De eigenschap IDENTITY kan worden toegewezen aan kolommen met kleineint, smallint, int, decimal(p,0) of numeriek(p;0). Er kan slechts één identiteitskolom per tabel worden gemaakt. Afhankelijke standaardwaarden en STANDAARDbeperkingen kunnen niet worden gebruikt met een identiteitskolom. U moet zowel het zaad als de verhoging opgeven, of geen van beide. Als geen van beide is opgegeven, is de standaardwaarde (1,1).

seed
De waarde die wordt gebruikt voor de eerste rij die in de tabel is geladen.

increment
De incrementele waarde die is toegevoegd aan de identiteitswaarde van de vorige rij die is geladen.

ROWGUIDCOL

Geeft aan dat de nieuwe kolom een globale unieke id-kolom voor rijen is. Slechts één uniqueidentifier kolom per tabel kan worden aangewezen als de kolom ROWGUIDCOL. De eigenschap ROWGUIDCOL kan alleen worden toegewezen aan een kolom uniqueidentifier.

NULL | NIET NULL

Geeft aan of null is toegestaan in de variabele. De standaardwaarde is NULL.

PRIMARY KEY

Een beperking die entiteitsintegriteit afdwingt voor een bepaalde kolom of kolommen via een unieke index. Er kan slechts één PRIMAIRE SLEUTELbeperking per tabel worden gemaakt.

UNIQUE

Een beperking die entiteitsintegriteit biedt voor een bepaalde kolom of kolommen via een unieke index. Een tabel kan meerdere UNIEKE beperkingen hebben.

GECLUSTERD | NIET-GECLUSTERD

Geef aan dat een geclusterde of niet-geclusterde index wordt gemaakt voor de beperking PRIMARY KEY of UNIQUE. PRIMAIRE SLEUTELbeperkingen maken gebruik van CLUSTERED en UNIEKE beperkingen maken gebruik van NIET-GECLUSTERD.

CLUSTERED kan slechts voor één beperking worden opgegeven. Als CLUSTERED is opgegeven voor een UNIEKE beperking en er ook een BEPERKING PRIMAIRE SLEUTEL is opgegeven, gebruikt de PRIMAIRE SLEUTEL NIET GECLUSTERD.

CHECK

Een beperking waarmee domeinintegriteit wordt afgedwongen door de mogelijke waarden te beperken die kunnen worden ingevoerd in een kolom of kolommen.

logical_expression
Een logische expressie die WAAR of ONWAAR retourneert.

<index_option>

Hiermee geeft u een of meer indexopties. Indexen kunnen niet expliciet worden gemaakt voor tabelvariabelen en er worden geen statistieken op tabelvariabelen bewaard. Vanaf SQL Server 2014 (12.x) is een nieuwe syntaxis geïntroduceerd waarmee u bepaalde indextypen inline kunt maken met de tabeldefinitie. Met deze nieuwe syntaxis kunt u indexen maken voor tabelvariabelen als onderdeel van de tabeldefinitie. In sommige gevallen kunnen de prestaties worden verbeterd met behulp van tijdelijke tabellen, die volledige indexondersteuning en statistieken bieden.

Zie CREATE TABLE voor een volledige beschrijving van deze opties.

Tabelvariabelen en rijschattingen

Tabelvariabelen hebben geen distributiestatistieken. In veel gevallen bouwt de optimizer een queryplan op basis van de veronderstelling dat de tabelvariabele nul rijen of één rij heeft. Raadpleeg voor meer informatie het gegevenstype tabel - Beperkingen en beperkingen.

Daarom moet u voorzichtig zijn met het gebruik van een tabelvariabele als u een groter aantal rijen verwacht (groter dan 100). Houd rekening met de volgende alternatieven:

• Tijdelijke tabellen kunnen een betere oplossing zijn dan tabelvariabelen wanneer het mogelijk is dat het aantal rijen groter is (groter dan 100).
• Voor query's die de tabelvariabele samenvoegen met andere tabellen, gebruikt u de hint RECOMPILE, waardoor de optimizer de juiste kardinaliteit voor de tabelvariabele gebruikt.
• In Azure SQL Database en te beginnen met SQL Server 2019 (15.x) geeft de tabelvariabele de uitgestelde compilatiefunctie kardinaliteitschattingen door die zijn gebaseerd op het werkelijke aantal tabelvariabele rijen, wat een nauwkeuriger aantal rijen biedt voor het optimaliseren van het uitvoeringsplan. Zie Intelligent queryverwerking in SQL-databases voor meer informatie.

Remarks

Variabelen worden vaak gebruikt in een batch of procedure als tellers voor WHILE, LOOP of voor een IF... ELSE-blok.

Variabelen kunnen alleen worden gebruikt in expressies, niet in plaats van objectnamen of trefwoorden. Als u dynamische SQL-instructies wilt maken, gebruikt u EXECUTE.

Het bereik van een lokale variabele is de batch waarin deze wordt gedeclareerd.

Een tabelvariabele is niet noodzakelijkerwijs geheugenbewoner. Onder geheugendruk kunnen de pagina's die behoren tot een tabelvariabele worden gepusht naar tempdb.

U kunt een inline-index definiëren in een tabelvariabele.

Een cursorvariabele waaraan momenteel een cursor is toegewezen, kan worden verwezen als bron in een:

• CLOSE statement
• DEALLOCATE statement
• FETCH statement
• OPEN statement
• Positioned DELETE- of UPDATE-instructie
• SET CURSOR variable statement (aan de rechterkant)

In al deze instructies genereert SQL Server een fout als er een cursorvariabele bestaat waarnaar wordt verwezen, maar er momenteel geen cursor aan is toegewezen. Als er geen cursorvariabele waarnaar wordt verwezen, wordt in SQL Server dezelfde fout gegenereerd voor een niet-declaratieve variabele van een ander type.

Een cursorvariabele:

• Dit kan het doel zijn van een cursortype of een andere cursorvariabele. Zie SET @local_variable (Transact-SQL) voor meer informatie.

• Kan worden verwezen als het doel van een uitvoercursorparameter in een EXECUTE-instructie als aan de cursorvariabele momenteel geen cursor is toegewezen.

• Moet worden beschouwd als een aanwijzer naar de cursor.

Examples

A. Using DECLARE

In het volgende voorbeeld wordt een lokale variabele met de naam gebruikt @find om contactgegevens op te halen voor alle achternamen die beginnen met Man.

USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

Hier is het resultatenoverzicht.

LastName            FirstName               Phone
------------------- ----------------------- -------------------------
Manchepalli         Ajay                    1 (11) 500 555-0174
Manek               Parul                   1 (11) 500 555-0146
Manzanares          Tomas                   1 (11) 500 555-0178

(3 row(s) affected)

B. DECLARE gebruiken met twee variabelen

In het volgende voorbeeld worden de namen van verkoopmedewerkers van Adventure Works Cycles opgehaald die zich in het Noord-Amerikaanse verkoopgebied bevinden en ten minste $ 2.000.000 in de verkoop voor het jaar hebben.

USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;

C. Een variabele van het type tabel declareren

In het volgende voorbeeld wordt een table variabele gemaakt waarin de waarden worden opgeslagen die zijn opgegeven in de OUTPUT-component van de UPDATE-instructie. Twee SELECT instructies volgen die de waarden in @MyTableVar en de resultaten van de updatebewerking in de Employee tabel retourneren. De resultaten in de INSERTED.ModifiedDate kolom verschillen van de waarden in de ModifiedDate kolom in de Employee tabel. Dit komt doordat de AFTER UPDATE trigger, die de waarde van ModifiedDate de huidige datum bijwerken, is gedefinieerd in de Employee tabel. De kolommen die worden geretourneerd uit OUTPUT weerspiegelen echter de gegevens voordat triggers worden geactiveerd. Zie OUTPUT Component (Transact-SQL)voor meer informatie.

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. Een variabele van het type tabel declareren, met inline-indexen

In het volgende voorbeeld wordt een table variabele gemaakt met een geclusterde inline-index en twee niet-geclusterde inlineindexen.

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    PRIMARY KEY CLUSTERED (EmpID),
    UNIQUE NONCLUSTERED (EmpID),
    INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO

De volgende query retourneert informatie over de indexen die in de vorige query zijn gemaakt.

SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO

E. Een variabele van het door de gebruiker gedefinieerde tabeltype declareren

In het volgende voorbeeld wordt een tabelwaardeparameter of tabelvariabele gemaakt met de naam @LocationTVP. Hiervoor is een bijbehorend door de gebruiker gedefinieerd tabeltype met de naam LocationTableTypevereist. Zie CREATE TYPE (Transact-SQL) voor meer informatie over het maken van een door de gebruiker gedefinieerd tabeltype. Zie Table-Valued Parameters (Database Engine) gebruiken voor meer informatie over parameters met tabelwaarden.

DECLARE @LocationTVP
AS LocationTableType;

Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)

F. Using DECLARE

In het volgende voorbeeld wordt een lokale variabele met de naam gebruikt @find om contactgegevens op te halen voor alle achternamen die beginnen met Walt.

-- Uses AdventureWorks

DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;

G. DECLARE gebruiken met twee variabelen

In het volgende voorbeeld worden variabelen gebruikt om de voor- en achternamen van werknemers in de DimEmployee tabel op te geven.

-- Uses AdventureWorks

DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;

See also

• EXECUTE (Transact-SQL)
• Ingebouwde functies (Transact-SQL)
• SELECT (Transact-SQL)
• table (Transact-SQL)
• Vergelijk getypte XML met niet-getypte XML