Динамический SQL для динамического LINQ в VB.NET с MS SQL Server 2008. Динамический sql ms sql

SPBDEV Blog - Создание динамического SQL в хранимой процедуре

Tags: SQL

Как построить динамический SQL в хранимой процедуре

После прочтения этой статьи вы поймете основы динамического SQL; как создавать инструкции на основе значений переменных и как выполнять эти сконструированные инструкции, используя sp_executesql и EXECUTE () из хранимой процедуры.

Все примеры этого урока основаны на Microsoft SQL Server Management и образцов баз данных, AdventureWorks и WideWorldImporters.

Создание динамического SQL в хранимой процедуре

Большинство SQL, которые мы пишем, записываются непосредственно в хранимую процедуру. Это то, что называется статическим SQL. Он называется так потому, что он не меняется. Как только он записан, его значение задано и не подлежит изменению.Ниже приведен пример статического SQL:

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = 1970

GROUP BY JobTitle

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = 1971

GROUP BY JobTitle

Обратите внимание, что есть две инструкции, каждая из которых возвращает резюме JobTitles для конкретного года рождения сотрудника. Если мы хотим добавить больше лет рождения, нам нужно добавить больше инструкций. Что нужно сделать, чтобы написать инструкцию единожды и изменять год на лету?

Именно здесь вступает в игру динамический SQL.

Динамический SQL - это SQL, который создается и выполняется во время исполнения. Это звучит сложно, но на самом деле это не так. Вместо того чтобы иметь инструкции, введенные непосредственно в хранимую процедуру, инструкции SQL сначала выстраиваются и определяются в переменных.

Затем в этих переменных выполняется код. Продолжая наш пример, вот тот же код с использованием динамического SQL:

DECLARE @birthYear int = 1970

DECLARE @statement NVARCHAR(4000)

WHILE @birthYear <= 1971

BEGIN

SET @statement = '

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR) +

' GROUP BY JobTitle'

EXECUTE sp_executesql @statement

SET @birthYear = @birthYear + 1

END

Динамический SQL выделен жирным шрифтом. Это SQL, который построен для каждого @birthYear. По мере создания SQL он сохраняется в @statement. Затем он выполняется с использованием sp_executesql, который мы объясним ниже.

Введение в sp_executesql

Вы можете использовать sp_executeslq для выполнения транзакционного SQL, хранящегося в переменной. Форма инструкции:

EXECUTE sp_executesql @statement.

Если вам интересно, sp_executesql - это системная хранимая процедура. Системные хранимые процедуры расширяют язык и предоставляют больше возможностей для использования.

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N'SELECT getdate()'

EXECUTE sp_executesql @statement

Если вы запустите это в окне запроса, вы получите подобный результат:

2018-01-24 18:49:30.143

Теперь, когда вы поняли, как работает sp_executeslq, давайте перейдем к практике. Предположим, вас попросили написать хранимую процедуру, которая возвращает либо среднее значение LineTotal, либо сумму LineTotal по ProductID для продуктов, отправленных в 2011 году.

Ваше руководство хотело бы, чтобы это было написано как хранимая процедура. Хранимая процедура должна принимать один параметр @ReturnAverage. Если это истинно, то вы вернете среднее значение, в противном случае сумму.

Конечно, вы могли бы написать это в виде двух отдельных запросов, как показано в следующей хранимой процедуре, но это было бы не очень весело, поскольку это предполагало бы слишком много ручного ввода и возможных ошибок.

CREATE PROCEDURE uspCalcuateSalesSummaryStatic

@returnAverage bit

IF (@returnAverage = 1)

BEGIN

SELECT SOD.ProductID,

AVG(SOD.LineTotal) as ResultAvg

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID

END

ELSE

BEGIN

SELECT SOD.ProductID,

SUM(SOD.LineTotal) as ResultSum

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID

END

Что здесь является слабым местом, так это много дублированного кода, который я выделил жирным шрифтом. Существует не так много уникального кода, но имеющийся выделен курсивом.

При всей этой избыточности у нас есть прекрасная возможность продемонстрировать некоторый динамический SQL. Давайте сделаем это!

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic

@returnAverage bit

DECLARE @statement NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = 'Avg'

ELSE SET @function = 'Sum'

SET @statement =

'SELECT SOD.ProductID,' +

@function + + '(SOD.LineTotal) as Result' + @function + '

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement

Здесь вместо двух полных версий SQL, один для AVG, другой для SUM, мы создаем запрошенную версию «на лету».

SQL построен и сохраняется в переменной @statement. Эта переменная построена на основе значения параметра @returnAverage. Если установлено значение 1, то @function представляет Среднее; в противном случае - Суммирование.

Отладка динамического SQL

Возможно, вам интересно, как выглядит SQL во время выполнения. Вы можете легко проверить код с помощью отладчика:

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

Продолжайте до тех пор, пока вы не прочитаете инструкцию Execute, выделенную ниже.

Использование отладчика

Как только вы достигнете этой инструкции, наведите указатель мыши на @statement, и когда появится подсказка инструмента, выберите текстовый визуализатор.

Отладчик является мощным и заслуживающим понимания. Я бы очень хотел, чтобы вы узнали об этом здесь.

Использование sp_executesql с параметрами

Вы можете использовать sp_executesql для задания параметров в вашей инструкции. Это в конечном итоге делает ваш код более легким для чтения и предоставляет некоторые преимущества оптимизации, поскольку оператор может быть скомпилирован один раз и повторно использован многократно.

Инструкция принимает форму:

EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …

Итак, давайте поясним детали.

@statement - это SQL, который мы хотим выполнить.
@parameterDefinition - это строка, содержащая определение всех параметров, указанных в @statement. Перечислен каждый параметр и тип, найденный @statement. Имя и тип разделяются пробелом. Несколько параметров разделяются запятой.

Затем мы устанавливаем значения параметров, задавая параметры и желаемое значение. Параметры перечислены в порядке, определенном в строке @parameterDefinition.

@ parm1 - это первый параметр, определенный в строке @parameterDefinition. Value - это значение, которое вы хотите установить.
@ parm2 - это второй параметр, если он определен, как указано в параметре @parameterDefinition.
и так далее…

Вот простой пример, который добавляет два числа, чтобы попробовать:

DECLARE @statement NVARCHAR(4000)

DECLARE @parameterDefinition NVARCHAR(4000)

SET @statement = N'SELECT @a + @b'

SET @parameterDefinition = N'@a int, @b int'

EXECUTE sp_executesql @statement, @parameterDefinition, @a=10, @b=5

Выделены различные части инструкции:

@statement (жирный шрифт) - обратите внимание, что он включает в себя 2 параметра: @a и @b. Также обратите внимание, что они не заявлены в TSQL. Скорее, они установлены в определении параметра.

@parameterDefinition (курсив) - каждый указанный параметр определяется как тип int.

Значения параметров (жирный шрифт+курсив) - здесь мы устанавливаем значение параметра.

Для этого в этом примере у нас есть динамически исполняемый оператор SQL, который добавляет два параметра.

Эти параметры определяются как целые числа. Значение каждого параметра устанавливается в команде sp_executesql.

Пример использования sp_executesql с параметрами

Давайте рассмотрим наш предыдущий пример и расширим его. Вместо того, чтобы жестко кодировать shipDate в запросе, как мы это сделали, давайте введем это как параметр. Это делает запрос более гибким и работает с годами, кроме 2011 года.

Чтобы внести это изменение, мы добавим параметр в нашу хранимую процедуру, а также в динамический запрос. Мы будем использовать команду sp_executesql для вызова динамического запроса с использованием этих параметров.

Обновленная хранимая процедура с изменениями показана ниже.

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

@returnAverage bit,

@shipDate int

DECLARE @statement NVARCHAR(4000),

@parameterDefinition NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = 'Avg'

ELSE SET @function = 'Sum'

SET @parameterDefinition = '@shipDateYear int'

SET @statement =

'SELECT SOD.ProductID,' +

@function + + '(SOD.LineTotal) as Result' + @function + '

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = @shipDateYear

GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDateYear=@shipDate

Чтобы запустить это, просто вызовите procpackSalesSummaryDynamic2 proc из окна запросов, используя следующую команду:

EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011

Если вы это сделаете, вы увидите следующие результаты.

Результаты запроса

Позвольте мне показать вам одно прекрасное упрощение, давайте объединим @shipDateYear и @shipDate в один параметр. Мы исключим @shipDateYear из нашего кода. Это облегчит отслеживание и чтение:

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

@returnAverage bit,

<span> @shipDate int

DECLARE @statement NVARCHAR(4000),

@parameterDefinition NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = 'Avg'

ELSE SET @function = 'Sum'

SET @parameterDefinition = '@shipDate int'

SET @statement =

'SELECT SOD.ProductID,' +

@function + + '(SOD.LineTotal) as Result' + @function + '

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = @shipDate

GROUP BY SOD.ProductID'

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDate

Обратите внимание, что инструкция EXECUTE намного проще, нет необходимости назначать параметр инструкции SQL @shipDateYear параметру хранимой процедуры parameter @ shipDate.

Это делает инструкцию более компактной и более легкой для чтения. Поток кажется лучше читаемым, поскольку вам не нужно мысленно устанавливать связи между параметрами хранимой процедуры и параметрами SQL.

Запуск динамического SQL с помощью EXECUTE ()

Вы также можете использовать команду EXEC или EXECUTE для запуска динамического SQL. Формат этой команды:

EXECUTE (@statement)

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N'SELECT getdate()'

EXECUTE (@statement)

Важно заключить @statement в круглые скобки. Если вы этого не сделаете, инструкция EXECUTE принимает @statement, и вместо запуска динамического SQL она решит, что значение переменной является именем хранимой процедуры. Вы получите следующую ошибку:

Msg 2812, Level 16, State 62, Line 3

Could not find stored procedure 'SELECT getdate()'.

Конечно, это дает отличную подсказку! Если хотите, можете использовать переменные, чтобы указать, какие хранимые процедуры вызывать.

sp_executesql против EXECUTE

Возможно, вам интересно, зачем использовать sp_executesql в сравнении с EXECUTE. Каковы различия между ними?

Вот несколько причин, по которым Microsoft рекомендует использовать sp_executesql для запуска динамического SQL:

С помощью EXECUTE все параметры могут быть преобразованы из своего исходного типа в Unicode. Это затрудняет способность оптимизатора сопоставлять динамически построенный SQL с уже существующим планом.
Используя sp_executesql, оптимизатор распознает параметры в динамическом SQL, что упрощает оптимизатор для соответствия планам.
Легче читать параметризованные запросы, чем читать кучу объединяющего их текста.
Параметрированные запросы менее подвержены атакам SQL-инъекций.

spbdev.biz

Динамические административные представления и функции (Transact-SQL)

03/07/2014
Время чтения: 2 мин

В этой статье

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

Важно!

Динамические административные представления и функции возвращают внутренние данные о состоянии, зависящие от реализации.Возвращаемые ими схемы и данные могут быть изменены в будущих выпусках сервера SQL Server.Поэтому в будущих выпусках динамические административные представления и функции могут быть несовместимы с динамическими административными представлениями и функциями сервера в этой версии.Например, в будущих версиях SQL Server определение любого динамического административного представления может быть расширено путем добавления столбцов в конец списка столбцов.Из-за того что число возвращаемых столбцов может измениться и нарушить работу приложения, использование синтаксиса SELECT * FROM dynamic_management_view_name в конечном коде не рекомендуется.

Есть два типа динамических административных представлений и функций:

динамические административные представления и функции области сервера. Для них необходимо разрешение VIEW SERVER STATE на сервере;
динамические административные представления и функции области базы данных. Для них необходимо разрешение VIEW DATABASE STATE на базе данных.

Запросы к динамическим административным представлениям

Ссылки на динамические административные представления в инструкциях языка Transact-SQL могут выполняться при помощи имен, состоящих из двух, трех и четырех частей. А ссылки на динамические административные функции в инструкциях языка Transact-SQL могут выполняться при помощи двух- или трехкомпонентных имен. Ссылаться на динамические административные представления и функции в инструкциях языка Transact-SQL с помощью однокомпонентных имен нельзя.

Все динамические административные представления и функции расположены в схеме sys и следуют соглашению по именованию dm_*. При использовании динамического административного представления или функции перед именем представления или функции должен стоять префикс схемы sys. Например, для обращения к динамическому административному представлению dm_os_wait_stats выполните следующий запрос:

SELECT wait_type, wait_time_ms

FROM sys.dm_os_wait_stats;

Необходимые разрешения

Для выполнения запроса к динамическому административному представлению и функции необходимо разрешение SELECT на объект, а также разрешения VIEW SERVER STATE или VIEW DATABASE STATE. Тем самым обеспечивается выборочное ограничение доступа пользователя или имени входа к динамическим административным представлениям и функциям. Для этого вначале создайте в базе данных master пользователя, а затем запретите для этого пользователя разрешение SELECT на динамические административные представления и функции, к которым не хотите предоставлять ему доступ. После этого пользователь не сможет делать выборку из этих представлений и результатов функций независимо от контекста базы данных пользователя.

Примечание

Так как инструкция DENY имеет более высокий приоритет, если пользователю было предоставлено разрешение VIEW SERVER STATE, но был запрет на разрешение VIEW DATABASE STATE, пользователь сможет получать данные области сервера, но не базы данных.

В этом разделе

Динамические административные представления и функции организованы в следующие категории:

Динамические административные представления управления и функции для групп доступности AlwaysOn	Динамические административные представления и функции, связанные с вводом-выводом
Динамические административные представления, относящиеся к системе отслеживания измененных данных	Динамические административные представления таблиц с оптимизированной памятью
Динамические административные представления, относящиеся к отслеживанию изменений	Динамические административные представления и соответствующие функции, связанные с объектами
Динамические административные представления, связанные со средой CLR	Динамические административные представления, связанные с уведомлениями запроса
Динамические административные представления, связанные с зеркальным отображением базы данных	Динамические административные представления, относящиеся к репликации
Динамические административные представления базы данных	Динамические административные представления для регулятора ресурсов
Динамические административные представления и соответствующие функции, связанные с выполнением	Динамические административные представления, связанные с безопасностью
Динамические административные представления расширенных событий	Динамические административные представления компонента Service Broker
Динамические административные представления Filestream and FileTable (Transact-SQL)	Динамические административные представления, относящиеся к операционной системе SQL Server
Динамические административные представления полнотекстового и семантического поиска	Динамические административные представления и функции, связанные с транзакциями
Динамические административные представления и функции, связанные с индексами

См. также

Справочник

GRANT, предоставление разрешений на сервер (Transact-SQL)

GRANT, предоставление разрешений на базу данных (Transact-SQL)

Системные представления (Transact-SQL)

msdn.microsoft.com

Динамические запросы SQL Server MS SQL Server

У меня есть 15 хранимых процедур, которые возвращают данные из общей таблицы, а затем присоединяются к этой таблице с определенной таблицей для извлечения инвентаря.

Есть ли способ передать имя «tblSpecific» в одну хранимую процедуру как переменную, например следующую?

То, как вы это делаете, – это динамически созданный SQL, который запускается через хранимую процедуру sp_executesql ().

В общем случае вы передаете свое имя требуемой таблицы в свою основную процедуру, создаете строку ncharvar SQL, которую хотите выполнить, и передайте ее в sp_executesql.

Проклятие и благословение Dynamic SQL – это лучшая страница, которую я видел для описания всех входящих и исходящих.

Одна из самых больших ошибок заключается в том, что если вы используете динамический SQL, то пользователь, который вызывает вашу хранимую процедуру, должен иметь не только разрешение на выполнение этой процедуры, но также должен иметь разрешение на доступ к базовым таблицам. Ссылка, которую я дал, также описывает, как обойти эту проблему.

Да, вы можете генерировать инструкцию SQL динамически, а затем выполнять ее.

Например,

DECLARE @specificTableName nvarchar(50) DECLARE @specificColumnName nvarchar(50) SET @specificTableName = 'tblSpecific' SET @specificColumnName = 'colSpecific' DECLARE @sql nvarchar(4000) set @sql = 'SELECT ... FROM tblCommon c INNER JOIN ' + @specificTableName + ' s ON c.PrimaryKey = s.' + @specificColumnName exec (@sql)

Сформулируйте / обработайте свой запрос в виде строки, а затем вызовите EXECUTE(@SQLStatement)

Динамический SQL опасен. Вы никогда не захотите подставлять переданные значения непосредственно в строку SQL. К счастью, похоже, что вы уже это знаете.

К сожалению, в этом случае вы обнаружили, что вы не можете использовать параметр SQL для имени таблицы. Так что делать? Вы не хотите использовать переданное значение в динамически генерируемом SQL, но вы не можете поставить его в запросе обычным безопасным способом.

Ответ – это таблица поиска. Создайте таблицу таблиц, в которой будет указано имя каждой из ваших конкретных таблиц. Он должен выглядеть примерно так:

CREATE TABLE [tables] (table_name sysname)

Затем вы можете написать запрос, который выглядит примерно так:

SELECT @tblSpecific = table_name FROM [tables] WHERE table_name = @tblSpecific

Теперь вам просто нужно проверить, является ли @tblSpecific NULL . Если это не так, то это безопасно использовать в динамическом выражении SQL (и динамический SQL – это, в конечном счете, ваш единственный вариант: даже определенная пользователем функция делает это на некотором уровне).

О, и еще одна вещь – мой выбор имен и типов для таблицы поиска – это не случайность. В стандарте SQL уже есть таблица, подобная этой (ну, в любом случае). Просто используйте INFORMATION_SCHEMA.Tables .

Альтернативно, если объем данных не слишком велик, вам может потребоваться рассмотреть определенную пользователем функцию, которая может вернуть переменную таблицы, к которой вы можете присоединиться.

SELECT .... FROM tblCommon c INNER JOIN dbo.SomeFuntionThatReturnsData(@someparam) s on c.primaryKey = s.foreignKey

Я бы сохранил их как отдельную хранимую процедуру.

Насколько это возможно, мне нравится хранить мои хранимые процедуры просто и просто. Они достаточно сложны, чтобы бросить взгляд, потому что выражения так сильно растягиваются, и добавление кучи процедурного кода, смешанного с фрагментами декларативного кода, просто усложняет его.

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

sqlserver.bilee.com

Динамический поиск запросов SQL MS SQL Server

Вставьте числовые значения, то есть @PriceMin, @PriceMax и @CityID, перед конкатенацией в SQL-заявлении.

Модифицированная инструкция sql выглядит следующим образом:

IF @PriceMin <> 0 OR @PriceMax <> 0 BEGIN SET @SQL = @SQL + ' AND PhotographerID IN(SELECT PhotographerID FROM Packages WHERE Price BETWEEN '+ cast(@PriceMin as varchar(10)) +' AND '+ cast(@PriceMax as varchar(10)) +') ' END IF @CityID > 0 SET @SQL += ' AND CityID = '+ cast(@CityID as varchar(10) )

Ответ Ромиля решает проблему, которую вы задали, но вы должны спросить, как изменить этот запрос, чтобы он не был динамичным. Я не знаю вашу структуру БД, поэтому этот запрос нужно будет проверить, но сделанные мной предложения предложения WHERE позволят вашему запросу работать без динамического использования. Это значительно увеличит производительность, уменьшит количество инъекций, и все вокруг – лучший способ для решения этих типов запросов.

ALTER PROCEDURE [dbo].[sp_Photographers_Select_Search] @Date AS VARCHAR(100), @PriceMin AS INT, @PriceMax AS INT, @CityID AS INT AS BEGIN SET DATEFORMAT DMY SELECT *, (SELECT TOP (1) Price FROM Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price) AS PriceMin, (SELECT TOP (1) Price FROM Packages WHERE PhotographerID = Photographers.PhotographerID ORDER BY Price DESC) AS PriceMax, (SELECT COUNT(GalleryID) FROM Galleries WHERE PhotographerID = Photographers.PhotographerID AND Status = 1) AS GalleryCount, (SELECT COUNT(CommentID) FROM Comments WHERE ContentID = Photographers.PhotographerID AND Status = 1 AND TypeID = 1) AS CommentCount FROM Photographers WHERE 1 = 1 AND PhotographerID NOT IN (SELECT PhotographerID FROM Appointments WHERE Date = @Date) AND ( ( @PriceMin = 0 AND @PriceMax = 0 ) OR PhotographerID IN (SELECT PhotographerID FROM Packages WHERE Price BETWEEN @PriceMin AND @PriceMax) ) AND ( @CityID = 0 OR CityID = @CityID ) END

Я бы сделал дальнейшие улучшения, чтобы переместить эти подзапросы в операторы JOIN в сочетании с операторами GROUP BY для повышения производительности, но для этого потребуются дополнительные знания БД.

sqlserver.bilee.com

Альтернатива динамическому SQL MS SQL Server

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

Проблема, с которой я сталкиваюсь, заключается в том, что запросы выполняются намного медленнее, если они не являются динамическими. В частности, предложение where намного быстрее в динамическом запросе из-за его повышенной гибкости. Например, предложение static where должно выглядеть примерно так:

where SomeColumn = case when @variable1 = 0 then SomeColumn else @variable1 end and( (@variable2 = -2 and SomeColumn2 = 1) or (@variable2 = -1) or (@variable2 = 0 and SomeColumn2 = 0 and SomeColumn3 = 0) or (@variable2 = 1 and SomeColumn2 = 0 and SomeColumn3 > 0) )

Но динамическое предложение where:

where ' + @SomeCondition + @SomeCondition2 + '

Используя пример дела:

declare @SomeCondition nvarchar(max) = case when @variable3 = -2 then N'Condition 1' when @variable3 = 0 then N'Condition 2' when @variable3 = 1 then N'Condition 3' else N'' end

Единственное решение, о котором я могу думать, это использование нескольких операторов if и изменение только предложения where в каждом выражении, но это кажется невероятно расточительным и трудоемким.

Существуют ли другие альтернативы динамическому SQL? В противном случае, есть ли что-то, что я могу сделать, чтобы заставить sql-сервер правильно направить меня к ошибке?

Solutions Collecting From Web of "Альтернатива динамическому SQL"

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

Кроме того, вы можете использовать ISNULL(NULLIF синтаксис ISNULL(NULLIF как ISNULL(NULLIF ниже), но используйте его с осторожностью, так как это может негативно повлиять на производительность.

where SomeColumn = ISNULL(NULLIF(@variable1,''),SomeColumn) and SomeColumn2 = ISNULL(NULLIF(@variable2,''),SomeColumn2) and and so on..

sqlserver.bilee.com

Условные соединения – динамический SQL MS SQL Server

DBA здесь на работе пытается превратить мои простые хранимые procs в динамическое sql monstrosity. По общему признанию, моя хранимая процедура может быть не такой быстрой, как хотелось бы, но я не могу не поверить, что есть адекватный способ сделать то, что в основном условное соединение.

UDF превращает список фильтров с разделителями-запятыми (например, названия банков) в таблицу.

Очевидно, что условие фильтра в предложении where не является идеальным. Любые предложения относительно лучшего способа условного соединения на основе хранимого параметра proc приветствуются. Кроме того, есть ли у кого-нибудь предложения относительно динамического подхода sql или против него?

Посмотрите здесь Динамические условия поиска в T-SQL

Вы можете использовать INNER JOIN в таблице, возвращенной из UDF, вместо того, чтобы использовать ее в предложении IN

Ваш UDF может быть чем-то вроде

CREATE FUNCTION [dbo].[csl_to_table] (@list varchar(8000) ) RETURNS @list_table TABLE ([id] INT) AS BEGIN DECLARE @index INT, @start_index INT, @id INT SELECT @index = 1 SELECT @start_index = 1 WHILE @index <= DATALENGTH(@list) BEGIN IF SUBSTRING(@list,@index,1) = ',' BEGIN SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT) INSERT @list_table ([id]) VALUES (@id) SELECT @start_index = @index + 1 END SELECT @index = @index + 1 END SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT) INSERT @list_table ([id]) VALUES (@id) RETURN END

а затем INNER JOIN на идентификаторах в возвращенной таблице. Этот UDF предполагает, что вы передаете INTs в свой список, разделенный запятыми

РЕДАКТИРОВАТЬ:

Чтобы обрабатывать значение null или no, передаваемое для @filter, наиболее простым способом, который я могу видеть, было бы выполнение другого запроса в sproc на основе значения @filter. Я не уверен, как это влияет на план выполнения кэширования (будет обновляться, если кто-то может подтвердить), или если конечный результат будет быстрее, чем исходный sproc, я думаю, что ответ здесь будет заключаться в тестировании.

Похоже, что переписывание кода рассматривается в другом ответе, но хорошим аргументом против динамического SQL в хранимой процедуре является то, что он нарушает цепочку прав собственности.

То есть, когда вы обычно вызываете хранимую процедуру, она выполняется под разрешениями владельца хранимой процедуры EXCEPT при выполнении динамического SQL с помощью команды execute, поскольку в контексте динамического SQL он возвращается обратно к разрешениям вызывающего абонента, что может быть нежелательным в зависимости от вашей модели безопасности.

В конце концов, вам, вероятно, лучше отказаться от компрометации и переписать его для решения проблем администратора баз данных, избегая динамического SQL.

Я не уверен, что понимаю ваше отвращение к динамическому SQL. Возможно, это то, что ваш UDF хорошо отвлекся от некоторой проблемы проблемы, и вы чувствуете, что динамический SQL вернет это. Ну, считайте, что большинство, если не все инструменты DAL или ORM будут сильно опираться на динамический SQL, и я думаю, что ваша проблема может быть переформулирована как «как я могу прекрасно абстрагироваться от беспорядочности динамического SQL».

Со своей стороны, динамический SQL дает мне именно тот запрос, который я хочу, а затем производительность и поведение, которые я ищу.

Я не вижу ничего плохого в вашем подходе. Переписывая его, чтобы использовать динамический SQL для выполнения двух разных запросов, основанных на том, является ли @Filter нулевым, кажется мне глупым, честно.

Единственный потенциальный недостаток, который я могу видеть из того, что у вас есть, это то, что это может вызвать определенные трудности при определении хорошего плана выполнения. Но если производительность достаточно хорошая, то нет причин для ее изменения.

Независимо от того, что вы делаете (и ответы здесь все имеют хорошие моменты), обязательно сравните планы производительности и исполнения каждого варианта.

Иногда ручная оптимизация просто бессмысленна, если она влияет на ремонтопригодность кода и действительно не создает никакой разницы в том, как выполняется код.

Сначала я просто посмотрю на изменение IN на простой LEFT JOIN с NULL check (это не избавит вас от вашего udf, но его нужно вызвать только один раз):

SELECT * FROM table LEFT JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter ON table.FilterField = filter.Value WHERE @Filter IS NULL OR filter.Value IS NOT NULL

Похоже, вы пытаетесь написать один запрос для работы с двумя сценариями: 1. @filter = "x, y, z" 2. @filter IS NULL

Чтобы оптимизировать сценарий 2, я бы включил INDER JOIN в UDF, а не использовал предложение IN …

SELECT * FROM table INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter ON table.FilterField = filter.Value

Чтобы оптимизировать для сценария 2, я бы не пытался адаптировать существующий запрос, вместо этого я намеренно делал эти случаи отдельными, либо инструкцией IF, либо UNION, и имитировал IF с предложением WHERE …

TSQL IF

IF (@filter IS NULL) SELECT * FROM table ELSE SELECT * FROM table INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter ON table.FilterField = filter.Value

СОЮЗ для имитации IF

SELECT * FROM table INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter ON table.FilterField = filter.Value UNION ALL SELECT * FROM table WHERE @filter IS NULL

Преимущество таких конструкций состоит в том, что каждый случай прост, и определение того, что является простым, самопросто. Однако объединение двух в один запрос приводит к таким компромиссам, как LEFT JOINs, и поэтому приводит к значительным потерям производительности для каждого.

sqlserver.bilee.com

sql - Динамический SQL для динамического LINQ в VB.NET с MS SQL Server 2008

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

Мне нужно представить следующий запрос с помощью LINQ:

DECLARE @PurchasedInventoryItemID Int = 2 DECLARE @PurchasedInventorySectionID Int = 0 DECLARE @PurchasedInventoryItem_PurchasingCategoryID Int = 3 DECLARE @PurchasedInventorySection_PurchasingCategoryID Int = 0 DECLARE @IsActive Bit = 1 DECLARE @PropertyID Int = 2 DECLARE @PropertyValue nvarchar(1000) = 'Granny Smith' --Property1, Property2, Property3 ... SELECT O.PurchasedInventoryObjectID, O.PurchasedInventoryObjectName, O.PurchasedInventoryConjunctionID, O.Summary, O.Count, O.PropertyCount, O.IsActive FROM tblPurchasedInventoryObject As O INNER JOIN tblPurchasedInventoryConjunction As C ON C.PurchasedInventoryConjunctionID = O.PurchasedInventoryConjunctionID INNER JOIN tblPurchasedInventoryItem As I ON I.PurchasedInventoryItemID = C.PurchasedInventoryItemID INNER JOIN tblPurchasedInventorySection As S ON S.PurchasedInventorySectionID = C.PurchasedInventorySectionID INNER JOIN tblPurchasedInventoryPropertyMap as M ON M.PurchasedInventoryObjectID = O.PurchasedInventoryObjectID INNER JOIN tblPropertyValue As V ON V.PropertyValueID = M.PropertyValueID WHERE I.PurchasedInventoryItemID = @PurchasedInventoryItemID AND S.PurchasedInventorySectionID = @PurchasedInventorySectionID AND I.PurchasingCategoryID = @PurchasedInventoryItem_PurchasingCategoryID AND S.PurchasingCategoryID = @PurchasedInventorySection_PurchasingCategoryID AND O.IsActive = @IsActive AND V.PropertyID = @PropertyID AND V.Value = @PropertyValue

Теперь я знаю, что запрос в .NET не выглядит так, это мой тест в SQL Design Studio. Естественно, переменные VB.NET будут использоваться вместо локальных переменных SQL.

Моя проблема заключается в следующем: все условия после "WHERE" являются необязательными. В этом случае может быть сделан запрос, который использует одно, несколько, все или ни одно из условий. V.PropertyID и V.Value также могут появляться любое количество раз.

В VB.NET я могу сделать этот запрос достаточно простым, просто конкатенируя строки и используя цикл для добавления условий "V.PropertyID/V.Value".

Я также могу создать хранимую процедуру в MS SQL, что достаточно просто.

Однако я хочу выполнить это с помощью LINQ.

Если бы кто-нибудь мог направить меня, я был бы очень благодарен.

qaru.site