SET @local_variable (Transact-SQL)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Mengatur variabel lokal yang ditentukan, yang sebelumnya dibuat dengan menggunakan pernyataan DECLARE @local_variable , ke nilai yang ditentukan.

Konvensi Sintaksis T-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, dan Azure SQL Managed Instance:

SET   
{ @local_variable  
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }  
}  
|  
{ @SQLCLR_local_variable.mutator_method  
}  
|  
{ @local_variable  
    {+= | -= | *= | /= | %= | &= | ^= | |= } expression  
}  
|   
  { @cursor_variable =   
    { @cursor_variable | cursor_name   
    | { CURSOR [ FORWARD_ONLY | SCROLL ]   
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
        [ TYPE_WARNING ]   
    FOR select_statement   
        [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]   
      }   
    }  
}   

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel:

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression  

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

@local_variable
Nama variabel dari jenis apa pun kecuali kursor, teks, ntext, gambar, atau tabel. Nama variabel harus dimulai dengan satu di tanda tangan (@). Nama variabel harus mengikuti aturan untuk pengidentifikasi.

property_name
Properti dari jenis yang ditentukan pengguna.

field_name
Bidang publik dari jenis yang ditentukan pengguna.

udt_name
Nama jenis yang ditentukan pengguna runtime bahasa umum (CLR).

{ . | :: }
Menentukan metode jenis yang ditentukan pengguna CLR. Untuk metode instans (non-statis), gunakan titik (.). Untuk metode statis, gunakan dua titik dua (::). Untuk memanggil metode, properti, atau bidang dari jenis yang ditentukan pengguna CLR, Anda harus memiliki izin EXECUTE pada jenis tersebut.

method_name(argumen [ , , ... n ] )
Metode jenis yang ditentukan pengguna yang mengambil satu atau beberapa argumen untuk mengubah status instans jenis. Metode statis harus publik.

@SQLCLR_local_variable
Variabel yang jenisnya terletak di perakitan. Untuk informasi selengkapnya, lihat Konsep Pemrograman Integrasi Common Language Runtime (CLR).

mutator_method
Metode dalam rakitan yang dapat mengubah status objek. SQLMethodAttribute.IsMutator diterapkan ke metode ini.

{ += | -= | *= | /= | %= | &= | ^= | |= }
Operator penugasan mapound:

+= Tambahkan dan tetapkan

-= Kurangi dan tetapkan

*= Mengalikan dan menetapkan

/= Bagi dan tetapkan

%= Modulo dan tetapkan

&= Bitwise DAN dan tetapkan

^= Bitwise XOR dan tetapkan

|= Bitwise OR dan tetapkan

ekspresi
Ekspresi apa pun yang valid.

cursor_variable
Nama variabel kursor. Jika variabel kursor target sebelumnya mereferensikan kursor yang berbeda, referensi sebelumnya akan dihapus.

cursor_name
Nama kursor yang dideklarasikan dengan menggunakan pernyataan DECLARE CURSOR.

KURSOR
Menentukan bahwa pernyataan SET berisi deklarasi kursor.

GULIR
Menentukan bahwa kursor mendukung semua opsi pengambilan: PERTAMA, TERAKHIR, BERIKUTNYA, PRIORITAS, RELATIF, dan ABSOLUTE. Anda tidak dapat menentukan SCROLL saat Anda juga telah menentukan FAST_FORWARD.

FORWARD_ONLY
Menentukan bahwa kursor hanya mendukung opsi FETCH NEXT. Kursor hanya diambil dalam satu arah, dari baris pertama hingga terakhir. Saat Anda menentukan FORWARD_ONLY tanpa kata kunci STATIC, KEYSET, atau DYNAMIC, kursor diimplementasikan sebagai DYNAMIC. Jika Anda tidak menentukan FORWARD_ONLY atau SCROLL, FORWARD_ONLY adalah default, kecuali Anda menentukan kata kunci STATIC, KEYSET, atau DYNAMIC. Untuk kursor STATIS, KEYSET, dan DINAMIS, SCROLL adalah default.

STATIS
Menentukan kursor yang membuat salinan sementara data yang akan digunakan oleh kursor. Semua permintaan ke kursor dijawab dari tabel sementara ini dalam tempdb. Akibatnya, modifikasi yang dilakukan pada tabel dasar setelah kursor dibuka tidak tercermin dalam data yang dikembalikan oleh pengambilan yang dibuat ke kursor. Dan, kursor ini tidak mendukung modifikasi.

SET KUNCI
Menentukan bahwa keanggotaan dan urutan baris dalam kursor diperbaiki saat kursor dibuka. Kumpulan kunci yang secara unik mengidentifikasi baris dibangun ke dalam keysettable dalam tempdb. Perubahan pada nilai non-kunci dalam tabel dasar, baik yang dibuat oleh pemilik kursor atau yang dilakukan oleh pengguna lain, terlihat sebagai pemilik kursor menggulir di sekitar kursor. Sisipan yang dibuat oleh pengguna lain tidak terlihat, dan sisipan tidak dapat dibuat melalui kursor server Transact-SQL.

Jika baris dihapus, upaya untuk mengambil baris mengembalikan @@FETCH_STATUS -2. Pembaruan nilai kunci dari luar kursor mirip dengan penghapusan baris lama diikuti dengan sisipan baris baru. Baris dengan nilai baru tidak terlihat, dan mencoba mengambil baris dengan nilai lama mengembalikan @@FETCH_STATUS -2. Nilai baru terlihat jika pembaruan terjadi melalui kursor dengan menentukan klausa WHERE CURRENT OF.

DINAMIS
Menentukan kursor yang mencerminkan semua perubahan data yang dibuat pada baris dalam tataan hasilnya sebagai pemilik kursor menggulir di sekitar kursor. Nilai data, pesanan, dan keanggotaan baris dapat berubah pada setiap pengambilan. Opsi pengambilan absolut dan relatif tidak didukung dengan kursor dinamis.

FAST_FORWARD
Menentukan kursor FORWARD_ONLY, READ_ONLY dengan pengoptimalan diaktifkan. FAST_FORWARD tidak dapat ditentukan saat SCROLL juga ditentukan.

READ_ONLY
Mencegah pembaruan dibuat melalui kursor ini. Kursor tidak dapat dirujuk dalam klausa WHERE CURRENT OF dalam pernyataan UPDATE atau DELETE. Opsi ini menggantikan kemampuan default kursor yang akan diperbarui.

KUNCI GULIR
Menentukan bahwa pembaruan atau penghapusan yang diposisikan yang dibuat melalui kursor dijamin berhasil. SQL Server mengunci baris saat dibaca ke dalam kursor untuk menjamin ketersediaannya untuk modifikasi nanti. Anda tidak dapat menentukan SCROLL_LOCKS kapan FAST_FORWARD juga ditentukan.

OPTIMIS
Menentukan bahwa pembaruan atau penghapusan yang diposisikan yang dibuat melalui kursor tidak berhasil jika baris diperbarui sejak dibaca ke dalam kursor. SQL Server tidak mengunci baris saat dibaca ke dalam kursor. Sebaliknya, ia menggunakan perbandingan nilai kolom tanda waktu, atau nilai checksum, jika tabel tidak memiliki kolom tanda waktu, untuk menentukan apakah baris dimodifikasi setelah dibaca ke dalam kursor. Jika baris dimodifikasi, upaya pembaruan atau penghapusan yang diposisikan gagal. Anda tidak dapat menentukan OPTIMIS saat FAST_FORWARD juga ditentukan.

TYPE_WARNING
Menentukan bahwa pesan peringatan dikirim ke klien ketika kursor dikonversi secara implisit dari jenis yang diminta ke jenis lain.

UNTUK select_statement
Adalah pernyataan SELECT standar yang menentukan kumpulan hasil kursor. Kata kunci UNTUK TELUSURI, dan INTO tidak diizinkan dalam select_statement deklarasi kursor.

Jika Anda menggunakan DISTINCT, UNION, GROUP BY, atau HAVING, atau Anda menyertakan ekspresi agregat dalam select_list, kursor dibuat sebagai STATIK.

Jika setiap tabel yang mendasar tidak memiliki indeks unik dan kursor ISO SCROLL atau jika kursor KEYSET Transact-SQL diminta, kursor secara otomatis adalah kursor STATIK.

Jika select_statement berisi klausa ORDER BY di mana kolom bukan pengidentifikasi baris unik, kursor DINAMIS dikonversi ke kursor KEYSET, atau ke kursor STATIC jika kursor KEYSET tidak dapat dibuka. Proses ini juga terjadi untuk kursor yang ditentukan dengan menggunakan sintaks ISO tetapi tanpa kata kunci STATIC.

BACA SAJA
Mencegah pembaruan dibuat melalui kursor ini. Kursor tidak dapat dirujuk dalam klausa WHERE CURRENT OF dalam pernyataan UPDATE atau DELETE. Opsi ini mengambil alih kemampuan default kursor yang akan diperbarui. Kata kunci ini bervariasi dari READ_ONLY sebelumnya dengan memiliki spasi alih-alih garis bawah antara READ dan ONLY.

UPDATE [OF column_name[ ,... n ] ]
Menentukan kolom yang dapat diperbarui dalam kursor. Jika OF column_name [,... n] disediakan, hanya kolom yang tercantum yang mengizinkan modifikasi. Ketika tidak ada daftar yang disediakan, semua kolom dapat diperbarui, kecuali kursor telah didefinisikan sebagai READ_ONLY.

Keterangan

Setelah variabel dideklarasikan, variabel diinisialisasi ke NULL. Gunakan pernyataan SET untuk menetapkan nilai yang bukan NULL ke variabel yang dideklarasikan. Pernyataan SET yang menetapkan nilai ke variabel mengembalikan satu nilai. Saat Anda menginisialisasi beberapa variabel, gunakan pernyataan SET terpisah untuk setiap variabel lokal.

Anda hanya dapat menggunakan variabel dalam ekspresi, bukan nama objek atau kata kunci. Untuk membuat pernyataan Transact-SQL dinamis, gunakan EXECUTE.

Aturan sintaks untuk SET @cursor_variable tidak menyertakan kata kunci LOKAL dan GLOBAL. Saat Anda menggunakan SET @cursor_variable = CURSOR... sintaksis, kursor dibuat sebagai GLOBAL atau LOKAL, tergantung pada pengaturan opsi default ke database kursor lokal.

Variabel kursor selalu lokal, bahkan jika mereka mereferensikan kursor global. Saat variabel kursor mereferensikan kursor global, kursor memiliki referensi kursor global dan lokal. Untuk informasi selengkapnya, lihat Contoh D, Menggunakan SET dengan kursor global.

Untuk informasi selengkapnya, lihat MENDEKLARASIKAN KURSOR (Transact-SQL).

Anda dapat menggunakan operator penetapan campuran di mana saja Anda memiliki penugasan dengan ekspresi di sisi kanan operator, termasuk variabel, dan SET dalam pernyataan UPDATE, SELECT, dan RECEIVE.

Jangan gunakan variabel dalam pernyataan SELECT untuk menggabungkan nilai (yaitu, untuk menghitung nilai agregat). Hasil kueri yang tidak terduga dapat terjadi karena semua ekspresi dalam daftar SELECT (termasuk penugasan) tidak selalu berjalan persis sekali untuk setiap baris output. Untuk informasi selengkapnya, lihat artikel KB ini.

Izin

Membutuhkan keanggotaan dalam peran publik. Semua pengguna dapat menggunakan SET @local_variable.

Contoh

Beberapa contoh menggunakan database sampel AdventureWorks.

J. Mencetak nilai variabel yang diinisialisasi dengan menggunakan SET

Contoh berikut membuat @myvar variabel, menempatkan nilai string ke dalam variabel, dan mencetak nilai @myvar variabel.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO  

B. Menggunakan variabel lokal yang diberi nilai dengan menggunakan SET dalam pernyataan SELECT

Contoh berikut membuat variabel lokal bernama @state dan menggunakan variabel lokal dalam SELECT pernyataan untuk menemukan nama depan dan belakang semua karyawan yang tinggal di status Oregon.

USE AdventureWorks2019;  
GO  
DECLARE @state CHAR(25);  
SET @state = N'Oregon';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City  
FROM HumanResources.vEmployee  
WHERE StateProvinceName = @state;
GO 

C. Menggunakan penetapan campuran untuk variabel lokal

Dua contoh berikut menghasilkan hasil yang sama. Setiap contoh membuat variabel lokal bernama @NewBalance, mengalikannya dengan 10, lalu menampilkan nilai baru variabel lokal dalam pernyataan SELECT . Contoh kedua menggunakan operator penetapan campuran.

/* Example one */  
DECLARE  @NewBalance  INT ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;
GO
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;
GO

D. Menggunakan SET dengan kursor global

Contoh berikut membuat variabel lokal lalu mengatur variabel kursor ke nama kursor global.

DECLARE my_cursor CURSOR GLOBAL   
FOR SELECT * FROM Purchasing.ShipMethod  
DECLARE @my_variable CURSOR ;  
SET @my_variable = my_cursor ;   
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable  
--(@my_variable) set to the my_cursor cursor.  

DEALLOCATE my_cursor;   
GO
--There is now only a LOCAL variable reference  
--(@my_variable) to the my_cursor cursor.

E. Menentukan kursor dengan menggunakan SET

Contoh berikut menggunakan SET pernyataan untuk menentukan kursor.

DECLARE @CursorVar CURSOR;  
  
SET @CursorVar = CURSOR SCROLL DYNAMIC  
FOR  
SELECT LastName, FirstName  
FROM AdventureWorks2019.HumanResources.vEmployee  
WHERE LastName like 'B%';  
  
OPEN @CursorVar;  
  
FETCH NEXT FROM @CursorVar;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    FETCH NEXT FROM @CursorVar  
END;  
  
CLOSE @CursorVar;  
DEALLOCATE @CursorVar;
GO

F. Menetapkan nilai dari kueri

Contoh berikut menggunakan kueri untuk menetapkan nilai ke variabel.

USE AdventureWorks2019;  
GO  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);  
SELECT @rows;
GO

G. Menetapkan nilai ke variabel jenis yang ditentukan pengguna dengan memodifikasi properti jenis

Contoh berikut menetapkan nilai untuk jenis yang ditentukan pengguna (UDT) Point dengan memodifikasi nilai properti X jenis .

DECLARE @p Point;  
SET @p.X = @p.X + 1.1;  
SELECT @p;  
GO  

Pelajari selengkapnya tentang membuat UDT yang Point dirujuk dalam contoh ini dan contoh berikut dalam artikel Membuat Jenis User-Defined.

H. Menetapkan nilai ke variabel jenis yang ditentukan pengguna dengan memanggil metode jenis

Contoh berikut menetapkan nilai untuk titik jenis yang ditentukan pengguna dengan memanggil metode SetXY jenis .

DECLARE @p Point;  
SET @p=point.SetXY(23.5, 23.5);  

i. Membuat variabel untuk jenis CLR dan memanggil metode mutator

Contoh berikut membuat variabel untuk jenis Point, lalu menjalankan metode mutator di Point.

CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE  
CREATE TYPE Point EXTERNAL NAME mytest.Point  
GO  
DECLARE @p Point = CONVERT(Point, '')  
SET @p.SetXY(22, 23);  

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

j. Mencetak nilai variabel yang diinisialisasi dengan menggunakan SET

Contoh berikut membuat @myvar variabel, menempatkan nilai string ke dalam variabel, dan mencetak nilai @myvar variabel.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT TOP 1 @myvar FROM sys.databases;

K. Menggunakan variabel lokal yang diberi nilai dengan menggunakan SET dalam pernyataan SELECT

Contoh berikut membuat variabel lokal bernama @dept dan menggunakan variabel lokal ini dalam SELECT pernyataan untuk menemukan nama depan dan belakang semua karyawan yang bekerja di Marketing departemen.

-- Uses AdventureWorks 
  
DECLARE @dept CHAR(25);  
SET @dept = N'Marketing';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name  
FROM DimEmployee   
WHERE DepartmentName = @dept;  

L. Menggunakan penetapan campuran untuk variabel lokal

Dua contoh berikut menghasilkan hasil yang sama. Mereka membuat variabel lokal bernama @NewBalance, mengalikannya dengan 10 dan menampilkan nilai baru variabel lokal dalam pernyataan SELECT . Contoh kedua menggunakan operator penetapan campuran.

/* Example one */  
DECLARE  @NewBalance INT;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  

M. Menetapkan nilai dari kueri

Contoh berikut menggunakan kueri untuk menetapkan nilai ke variabel.

-- Uses AdventureWorks 
  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);  
SELECT TOP 1 @rows FROM sys.tables;  

Langkah berikutnya

Pelajari selengkapnya tentang konsep terkait dalam artikel berikut:

• Operator Majemuk (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• EXECUTE (Transact-SQL)
• SELECT (Transact-SQL)
• Pernyataan SET (Transact-SQL)