Формирование запроса Dynamic SQL

Иногда, при формировании запроса к базе данных необходимо использовать данные из ранее полученных запросов. То есть, например, в качестве имени запрашиваемого поля или таблицы использовать данные получаемые динамически в цикле. Для этих целей существует dynamic sql.

При использовании dynamic sql запрос формируется как строка:

SET @SQLQuery='SELECT @Customer_Svc_ID='+@Table_Name+'_id FROM Customers WHERE Customer_ID='+CONVERT(nvarchar(10),@Customer_ID);

Использование динамических запросов возможно только из хранимых процедур и невозможно из функций. При попытке выполнить динамический запрос из функции вы получите сообщение:

Only functions and some extended stored procedures can be executed from within a function.

Формирование динамического запроса, выполнение его и возврат результата включает этапы:

Вызов хранимой процедуры с параметрами, один из которых (необязательно) - переменная куда будет записан результат.
В процедуре формируется строка динамического запроса, включающая в себя переданные в процедуру параметры.
Динамический запрос выполняется и возвращает результат в хранимую процедуру.
Хранимая процедура возвращает результат в вызвавший её код (приложение или другая хранимая процедура)

Динамические запросы выполняются с помощью ключевого слова EXEC/EXECUTE или хранимой процедуры sp_executesql. Исполнение динамических запросов с помощью EXEC/EXECUTE считается довольно неудобным и очень небезопасным. Поэтому для исполнения динамических запросов рекомендуется использовать хранимую процедуру sp_executesql.
В моем случае мне нужно было выполнить пару динамических запросов для получения нужного результата:

ALTER PROCEDURE [dbo].[get_svc_quantity]
(	@Customer_ID int,
	@Svc_Name nvarchar(30),
	@Month nvarchar(30)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLQuery nvarchar(1000),
		@Billing_Event_Name nvarchar(200),
		@Customer_Svc_ID nvarchar(100),
		@Table_Name nvarchar(20),
		@Quantity int

SELECT @Billing_Event_Name = Billing_Event_Name FROM HZN_Subscription_Services WHERE Name=@Svc_Name
SELECT @Table_Name = Table_Name FROM HZN_Subscription_Services WHERE Name=@Svc_Name
----------------
SET @SQLQuery='SELECT @Customer_Svc_ID='+@Table_Name+'_id FROM Customers WHERE Customer_ID='+CONVERT(nvarchar(10),@Customer_ID);
exec sp_executesql @SQLQuery, N' @Customer_Svc_ID nvarchar(100) OUTPUT', @Customer_Svc_ID OUTPUT
----------------------
SET @SQLQuery='SELECT @Quantity = Quantity FROM '+ @Table_Name + ' WHERE id=@Customer_Svc_ID AND Month=@Month AND Billing_Event_Name=@Billing_Event_Name AND id=@Customer_Svc_ID'
exec sp_executesql @SQLQuery, N'@Customer_Svc_ID nvarchar(100), @Month nvarchar(30), @Billing_Event_Name nvarchar(200), @Quantity int OUTPUT', @Customer_Svc_ID=@Customer_Svc_ID, @Month=@Month, @Billing_Event_Name=@Billing_Event_Name, @Quantity = @Quantity OUTPUT
SELECT @Quantity AS Quantity
RETURN
END

Формирование самого запроса обычно проблем не вызывает. Это обычная конкатенация строк. При использовании sp_executesql переменные в запросе не нужно как-то дополнительно обрабатывать с помощью кавычек. Просто на месте переменной пишем ее имя. Например - @Customer_Svc_ID.
При вызове sp_executesql этой процедуре нужно передать текст запроса, список переменных и их типов и значения переменных.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Для того, чтобы динамический запрос мог мог возвращать значение (чтобы потом его использовать в родительской процедуре) в него нужно передать переменную, которая пример это значение. например:

SELECT @Variable = .....

А в списке переменных обозначить эту переменную как OUTPUT.

SET @SQLQuery='SELECT @Quantity = Quantity FROM '+ @Table_Name + ' WHERE id=@Customer_Svc_ID AND Month=@Month AND Billing_Event_Name=@Billing_Event_Name AND id=@Customer_Svc_ID'
exec sp_executesql @SQLQuery, N'@Customer_Svc_ID nvarchar(100), @Month nvarchar(30), @Billing_Event_Name nvarchar(200), @Quantity int OUTPUT', @Customer_Svc_ID=@Customer_Svc_ID, @Month=@Month, @Billing_Event_Name=@Billing_Event_Name, @Quantity = @Quantity OUTPUT

В конце хранимой процедуры, которая должна возвращать значение нужно явно выполнить SELECT AS, чтобы значение (или значения) возвращалось как таблица и к элементам можно было обращаться по имени. В моем случае:

SELECT @Quantity AS Quantity

Вызов процедуры, содержащей динамический запрос, выполняется так:

EXEC get_svc_quantity 3, 'VOIP_Ext_BB', 'April 2018'

То есть указывается ключевое слово EXEC, затем имя процедуры и список параметров через запятую.

В коде C# вызов процедуры и обработка результата выполняется также как и любой другой запрос:

SqlCommand get_selected_month_quantity = new SqlCommand("EXEC get_svc_quantity " + HZN_Subscriptions_Customers.SelectedValue.ToString() + ", '" +
                                                                servicename + "', '" + Month_DropDownList.SelectedValue.ToString() + " " +
                                                                Year_DropDownList.SelectedValue.ToString() + "';", conn);
//EXEC get_svc_quantity 3, 'VOIP_Ext_BB', 'April 2018'
using (SqlDataReader selected_month_quantity = get_selected_month_quantity.ExecuteReader())
{
    selected_month_quantity.Read();
    Cells[2].Text = selected_month_quantity["Quantity"].ToString();
}

Для того, чтобы результаты, возвращаемые процедурой можно было использовать в других (вызывающих процедуру с динамическим запросом) процедурах, процедуре с динамическим запросом нужно передать в качестве параметра переменную, в которую будет записано возвращаемое значение. Это не нужно делать для процедур, вызываемых из приложения, но нужно для процедур вызываемых из других процедур. Для использования в других процедурах моя процедура будет выглядеть так:

ALTER PROCEDURE [dbo].[get_svc_quantity_]
(	@Customer_ID int,
	@Svc_Name nvarchar(30),
	@Month nvarchar(30),
	@Quantity int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLQuery nvarchar(1000),
		@Billing_Event_Name nvarchar(200),
		@Customer_Svc_ID nvarchar(100),
		@Table_Name nvarchar(20)

SELECT @Billing_Event_Name = Billing_Event_Name FROM HZN_Subscription_Services WHERE Name=@Svc_Name
SELECT @Table_Name = Table_Name FROM HZN_Subscription_Services WHERE Name=@Svc_Name
----------------
SET @SQLQuery='SELECT @Customer_Svc_ID='+@Table_Name+'_id FROM Customers WHERE Customer_ID='+CONVERT(nvarchar(10),@Customer_ID);
exec sp_executesql @SQLQuery, N' @Customer_Svc_ID nvarchar(100) OUTPUT', @Customer_Svc_ID OUTPUT
----------------------
SET @SQLQuery='SELECT @Quantity = Quantity FROM '+ @Table_Name + ' WHERE id=@Customer_Svc_ID AND Month=@Month AND Billing_Event_Name=@Billing_Event_Name AND id=@Customer_Svc_ID'
exec sp_executesql @SQLQuery, N'@Customer_Svc_ID nvarchar(100), @Month nvarchar(30), @Billing_Event_Name nvarchar(200), @Quantity int OUTPUT', @Customer_Svc_ID=@Customer_Svc_ID, @Month=@Month, @Billing_Event_Name=@Billing_Event_Name, @Quantity = @Quantity OUTPUT

RETURN
END

И вызов ее можно делать так:

DECLARE @q int
EXEC get_svc_quantity_ 3, 'VOIP_Ext_BB', 'April 2018', @q OUTPUT

PRINT @q

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

Real name:

E-Mail:

Address:

Enter your comment. Wiki syntax is allowed:

Please fill all the letters into the box to prove you're human. Please keep this field empty:

Subscribe to comments

Формирование запроса Dynamic SQL

Выполнение запроса Dynamic SQL

Использование значений, возвращаемых динамическим запросом

Использование результатов, возвращаемых процедурой с динамическими запросами

C#

В хранимых процедурах SQL

Discussion