Dlaczego nie mogę używać zmiennych w T-SQL, tak jak sobie wyobrażam, że mogę?

Wybacz, jestem programistą, który przeniósł się do świata SQL. Myślałem, że mogę poprawić trochę SQL, dodając zmienne, ale nie działało to tak, jak się spodziewałem. Czy ktoś może mi powiedzieć, dlaczego to nie działa? Nie chcę się obejść, chcę poznać powody, dla których to nie działa tak, jak wyobrażam sobie, że powinno, bo jestem pewien, że jest dobry powód, ale obecnie nie wyskakuje na mnie.

DECLARE @DatabaseName varchar(150)
SET @DatabaseName = 'MyAmazingDatabaseName'

CREATE DATABASE @DatabaseName
GO

USE @DatabaseName
GO

Na stronie online Książki dla zmiennych

Zmiennych można używać tylko w wyrażeniach, a nie w nazwach obiektów lub słowach kluczowych. Aby zbudować dynamiczne instrukcje SQL, użyj EXECUTE.

Działa to tak, jak się spodziewałeś, jeśli na przykład użyjesz zmiennej w klauzuli where. Jeśli tak, to sądzę, że ma to coś wspólnego z tym, że parser nie jest w stanie ocenić zmiennej, a tym samym sprawdzić istnienia. Podczas wykonywania zapytanie jest najpierw analizowane pod kątem składni i obiektów, a następnie, jeśli parsowanie zakończy się powodzeniem, zapytanie zostanie wykonane w momencie, w którym zmienna zostanie ustawiona.

DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;

Ograniczenia użycia zmiennych w instrukcjach SQL wynikają z architektury SQL.

Przetwarzanie instrukcji SQL składa się z trzech faz:

1. Przygotowanie - Instrukcja jest analizowana i kompilowany jest plan wykonania , określający, do których obiektów bazy danych można uzyskać dostęp, w jaki sposób są one dostępne i jak są powiązane. Plan wykonania jest zapisywany w pamięci podręcznej planu .
2. Wiązanie - wszelkie zmienne w instrukcji są zastępowane rzeczywistymi wartościami.
3. Wykonanie - buforowany plan jest wykonywany z powiązanymi wartościami.

Serwer SQL ukrywa krok przygotowawczy przed programistą i wykonuje go znacznie szybciej niż bardziej tradycyjne bazy danych, takie jak Oracle i DB2. Ze względów wydajnościowych SQL spędza potencjalnie dużo czasu na określeniu optymalnego planu wykonania, ale robi to tylko przy pierwszym napotkaniu instrukcji po ponownym uruchomieniu.

Tak więc w statycznym SQL zmienne mogą być używane tylko w miejscach, w których nie unieważnią planu wykonania, a więc nie dla nazw tabel, nazw kolumn (w tym nazw kolumn w warunkach GDZIE) itp.

Dynamiczny SQL istnieje w przypadkach, gdy nie można obejść ograniczeń, a programista wie, że jego wykonanie potrwa nieco dłużej. Dynamiczny SQL może być podatny na wstrzykiwanie złośliwego kodu, więc uważaj!

Jak widać, pytanie „dlaczego” wymaga innego rodzaju odpowiedzi, w tym uzasadnienia historycznego i podstawowych założeń dotyczących języka, nie jestem pewien, czy naprawdę mogę oddać sprawiedliwość.

Ten obszerny artykuł autorstwa SQL MVP Erlanda Sommarskoga zawiera uzasadnienie wraz z mechaniką:

Klątwa i błogosławieństwa dynamicznego SQL :

Plany zapytań w pamięci podręcznej

Każde zapytanie uruchamiane w SQL Server wymaga planu zapytań. Gdy uruchamiasz zapytanie po raz pierwszy, SQL Server tworzy dla niego plan zapytań - lub, zgodnie z terminologią - kompiluje zapytanie. Program SQL Server zapisuje plan w pamięci podręcznej, a przy następnym uruchomieniu zapytania plan zostanie ponownie użyty.

To (i bezpieczeństwo, patrz poniżej) jest prawdopodobnie największym powodem.

SQL działa przy założeniu, że zapytania nie są operacjami jednorazowymi, ale będą używane w kółko. Jeśli tabela (lub baza danych!) Nie jest faktycznie określona w zapytaniu, nie ma możliwości wygenerowania i zapisania planu wykonania do wykorzystania w przyszłości.

Tak, nie każde uruchamiane przez nas zapytanie zostanie ponownie użyte, ale jest to domyślna przesłanka działania SQL , więc „wyjątki” mają być wyjątkowe.

Kilka innych powodów, dla których Erland wymienia (zauważ, że wyraźnie wymienia zalety korzystania z procedur przechowywanych , ale wiele z nich to także zalety sparametryzowanych (niedynamicznych) zapytań):

• System uprawnień : aparat SQL nie jest w stanie przewidzieć, czy masz uprawnienia do uruchomienia zapytania, jeśli nie zna tabeli (lub bazy danych), z którą będziesz działał. „Łańcuchy uprawnień” za pomocą dynamicznego SQL są uciążliwe.
• Zmniejszenie ruchu w sieci : przekazanie nazwy przechowywanego proc i kilku wartości parametrów przez sieć jest krótsze niż długie zapytanie.
• Enkapsulacja logiki : powinieneś zapoznać się z zaletami enkapsulacji logiki z innych środowisk programowania.
• Śledzenie, co jest używane : Jeśli muszę zmienić definicję kolumny, jak mogę znaleźć cały kod, który ją wywołuje? Istnieją procedury systemowe do znajdowania zależności w bazie danych SQL, ale tylko wtedy, gdy kod znajduje się w procedurach przechowywanych.
• Łatwość pisania kodu SQL : sprawdzanie składni następuje podczas tworzenia lub modyfikowania procedury składowanej, więc mam nadzieję, że będzie mniej błędów.
• Rozwiązywanie problemów i problemów : DBA może znacznie łatwiej śledzić i mierzyć wydajność poszczególnych procedur przechowywanych niż zmieniający się dynamiczny SQL.

Ponownie, każdy z nich ma sto niuansów, których nie będę tu wchodził.

Musisz użyć dynamicznego SQL

DECLARE @DatabaseName varchar(150) = 'dbamaint'
declare @sqltext nvarchar(max) = N''

set @sqltext = N'CREATE DATABASE '+quotename(@DatabaseName)+ ';'

print @sqltext 

-- once you are happy .. uncomment below
--exec sp_executesql @sqltext
set @sqltext = ''
set @sqltext = N'use '+quotename(@DatabaseName)+ ';'
print @sqltext 
-- once you are happy .. uncomment below
--exec sp_executesql @sqltext

poniżej znajduje się wynik drukowania. Gdy usuniesz komentarz, exec sp_executesql @sqltextinstrukcje zostaną faktycznie wykonane ...

CREATE DATABASE [dbamaint];
use [dbamaint];