SSRS - игнорировать переменные SQL в моем запросе. Переменные в sql запросе
Переменные
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE@Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символовUnicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar(16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLAREчерез запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменныеaиb:
DECLARE
@a int,
@b int
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULLи до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQLпредусмотрены два способа задания значения переменной — для этой цели можно использовать операторSELECTилиSET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что операторSELECTпозволяет получить исходное присваиваемое значение из таблицы, указанной в оператореSELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @a = 1;
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
DECLARE @c int
SET @c = COUNT(*) FROM City
SELECT @c
а следующий оператор выполняется вполне успешно:
DECLARE @c int
SET @c = (SELECT COUNT(*) FROM City)
SELECT @c
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
DECLARE @c int
SELECT @c = COUNT(*) FROM City
SELECT @c
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще — с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int;
SET @IdProd = 1;
SELECT [Description]
FROM Product
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQLServerимеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запросаSELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONSдля извлечения количества подключений кSQLServerсо времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
@@ERROR- Содержит номер ошибки, возникшей при выполнении последнего оператораT-SQLв текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
@@IDENTITY- Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператораINSERT. Если в последнем оператореINSERTне произошла выработка идентификационного значения, системная переменная @@IDENTITYсодержитNULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
@@ROWCOUNT- Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператораDELETEс конструкциейWHEREколичество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.
! Следует отметить, что с версии SQLServer2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.
studfiles.net
Visual Basic: передача переменных в SQL-запрос стандартом. Butterfly Design
Начнем с простого. Пусть у нас имеется база данных DataBase.mdb и пусть в ней присутствует таблица tblAdres, а в этой таблице присутствуют поля fieldCity, fieldStreet, fieldHouse, fieldSutname и fieldTelefon. Нам нужно выбрать адресатов, проживающих в г. Владивосток.
В зависимости от того, какую технологию вы используете (DAO/ADO), подключите соответствующие библиотеки и объявите в требуемом формате переменные Database и Recordset.Dim strSQL As String strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = Владивосток” rst.Open strSQL ‘предполагается, что объекты db.ADODB и rst.ADODB уже объявлены. Do Until rst.EOF ‘предполагается, что есть в наличии элемент управления ListBox1, куда мы будем передавать записи ListBox1.AddItem rst.Fields(“fieldCity”) & “ “ & rst.Fields(“fieldStreet”) & “ “rst.Fields(“fieldHouse”) & “ “ & _ rst.Fields(“fieldSurname”) & “ “rst.Fields(“fieldTelefon”) & vbCrLf rst.MoveNext Loop
Обратите внимание, весь запрос помещен внутри двойных кавычек. Чтобы Jet «понял», что в запросе имеется переменная, ее необходимо вынести за пределы кавычек. В нашем случае это будет выглядеть так:
Dim strCity As String strCity = “Владивосток” strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & strCity rst.Open strSQL
Данный запрос уже близок к рабочему, но при попытке его выполнить вы, скорее всего, получите сообщение об ошибке № 3061: Too few parameters. Expected 1. (Слишком мало параметров. Ожидалось 1). Дело в том, что спецификации SQL предписывают ограничивать одинарными кавычками передаваемый параметр. Джеффри П. Мак-Манус в своей книге [1] рекомендует использовать функцию, ограничивающую строковую переменную одинарными кавычками.
Private function Quote(strVariable As String) As String Quote = “’” & strVariable & “’” End function
strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & Quote(strCity) rst.Open strSQL
Могу вас заверить, что эта комбинация будет работать, если вы, конечно, добавили в свой проект функцию Quote(). Но если вы не хотите этого делать, вам достаточно слегка «модернизировать» переменную strCity:
strCity = “Владивосток” strCity = “’” & strCity & “’” Dim strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & strCity rst.Open strSQL
Теперь посмотрим, как передать в запрос несколько переменных. Предположим, нам надо выбрать записи по жителям города Владивосток, имеющих фамилию Иванов.
Dim strCity As String Dim strSurname As String strCity = “Владивосток” strSurname = “Иванов” strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & Quote(strCity) & “ AND [fieldSurname] = “ & Quote(strSurname)
Строка запроса немного усложнилась, но структура осталась все та же. В подобных случаях целесообразно разбить строку на несколько частей, иначе целиком она не поместится на экране редактора, и работать с ней будет крайне неудобно. Я рекомендую разбить строку запроса так, чтобы каждая SQL-директива находилась на новой строке. Так будет легче редактировать строку и отслеживать в нем ошибки.
strSQL = “SELECT * FROM [tblAdres] “ & _ “ WHERE [fieldCity] = ” & Quote(strCity) & _ “ AND [fieldSurname] = “ & Quote(strSurname) rst.Open strSQL
Обратите внимание на пробел между кавычками и директивой AND. При использовании переменных в запросе трудно заметить необходимость этого пробела, но без него вы можете получить сообщение об ошибке №3131: Syntax error in FROM clause (синтаксическая ошибка в пункте FROM). Действительно, если вы еще раз взглянете на строку запроса в предыдущем примере, то заметите, что без этого пробела директива AND «прилипнет» к переменной Quote(strCity), что и вызовет ошибку. Чтобы оградить себя от подобных недоразумений, я перед всеми SQL-директивами ставлю пробел.
Теперь посмотрим, как можно передать переменную в имя таблицы или имя поля. Ограничить их одинарными кавычками не получится — мы получим сообщение об ошибке №3450: Syntax error in query. Incomplete query clause (Синтаксическая ошибка в запросе. Неполная строка запроса). В этом случае, используя все туже конкатенацию необходимо «уложить» переменную в квадратные скобки. Пусть наша таблица называется [tbl Adres] и имеет поля [field Surname], [field City], [field Street] и [field House]. Допустим, нам необходимо выбрать адреса граждан, проживающих в городе, который укажет пользователь, и имеющих фамилию, которую укажет пользователь. Добавим в проект два текстовых поля: txtPeople и txtCity.
Dim strTable As String Dim strFieldPeople As String Dim strFieldCity As String Dim strFieldStreet As String Dim strFieldHouse As String Dim strPeople As String Dim strCity As String
strTable = “[tbl Adres]” ‘наименование таблицы strTable = “[tbl Surnma]” ‘наименование поля таблицы ‘Фамилия’ strTable = “[tbl City]” ‘наименование поля таблицы ‘Город’ strTable = “[tbl Street]” ‘наименование поля таблицы ‘Улица’ strTable = “[tbl House]” ‘наименование поля таблицы ‘Дом’
strPeople = Trim(txtPeople.Text) ‘даем пользователю возможность ввести критерий выборки, strCity = Trim(txtCity.text) ‘и сразу ‘обрезаем’ лишние пробелы
>strSQL = “SELECT “ & strFieldStreet & “, “ & strFieldHouse & _ “ FROM “ & strTable & _ “ WHERE “ & strFieldPeople & “ = “ & Quote(strPeople) & _ “ AND “ & strFieldCity & “ = “ & Quote(strCity) rst.Open strSQL
Как вы, наверное, заметили, данный SQL-запрос полностью состоит из переменных (кроме директив самого запроса). К тому же в данном примере использовались названия таблиц, состоящие из нескольких слов. При такай структуре запроса количество слов в названии таблицы становится не существенным.
Рассмотрим еще несколько примеров. Допустим, у нас есть база данных, которая содержит таблицу tblOperations, которая в свою очередь имеет поля [fieldNumber], [fieldData], [fieldNameCompany] и [fieldSumm]. Первое поле содержит номер заказа, второе дату, третье имя компании а четвертое сумму операции. Допустим, вам необходимо дать пользователям возможность задавать критерии выборки по имени компании. Причем, если поле не заполнено, тогда recordset должен содержать полный набор записей, а если заполнено, тогда с критерием выборки (такой прием часто применяют при организации поиска в базе данных). В этом случае (и в более сложных так же) можно обойтись вполне одним SQL-запросом. Посмотрим, как это реализовать на практике: «Нарисуем» в форме текстовое поле txtCompanyName.
Dim strCompanyName As String If txtCompanyName <> Empty Then strCompanyName = “= “ & Quote(txtCompanyName.Text) Else strCompanyName = “<> Null” ‘если вы конечно уверены, что в вашей базе нет пустых записей
End if strSQL = “SELECT * & _ “ FRM tblOperations “ & _ “ WHERE [fieldNameCompany] “ & strCompanyName rst.Open strSQL
Приведенный фрагмент кода выберет все записи таблицы, если поле txtCompanyName не заполнено. Но если его заполнить, тогда recordset будет выбирать записи по критерию совпадения с введенным в поле txtCompanyName значением. О том, как сделать выборку по дате мы поговорим чуть позже, а пока вернемся к приведенному примеру. Обратите внимание, в запросе между директивой WHERE [fieldNameCompany] т переменной нет математического знака — мы вынесли его за пределы запроса и сделали частью переменной. Хочу также обратить ваше внимание на то, что процессор баз данных интерпритирует пустое поле таблицы как Null, а не как Empty. Поэтому использовать значение Empty при построении SQL-запроса нельзя.
В одном из примеров мы уже присваивали строковой переменной весь SQL-запрос, тоже самое можно сделать с любое его частью, даже той, которая включает директивы SQL. Вот как может выглядеть предыдущий пример:
Dim strCompanyName As String If txtCompanyName <> Empty Then strCompanyName = “ WHERE [fieldNameCompany] = “ & Quote(txtCompanyName.Text) Else strCompanyName = ““ WHERE [fieldNameCompany] <> Null “
>End if strSQL = “SELECT * & _ “ FRM tblOperations “ & _ strCompanyName rst.Open strSQL
Если вы обратили внимание, я постоянно упоминаю о строковых переменных. Дело в том, что кроме них в SQL-запрос можно передать только целочисленные переменные (byte, integer и long), а все остальные придется переводить в строковый тип. Отдельного внимания заслуживают переменные типа Data, но о них чуть позже. Давайте посмотрим, как проще всего перевести в строковый тип такие типы данные, как single, double и currency. Для этого проще всего использовать встроенную в VB функцию преобразования с сроковый тип str(variable).
Dim curPrice As Currency Dim strPrice As String curPrice = 44.56 strPrice = str(strPrice) ‘ не забудьте «одеть» ее в одинарные кавычки
У вас также может появиться необходимость использовать в запросе переменную типа Boolean. Например, если необходимо сделать выборку данных по полю, содержащих логическое значение. Для этой цели можно воспользоваться функцией IIf.
Dim varBoolean As Boolean varBoolean = True strSQL = “SELECT * “ & _ “ FROM [tblAdres] “ & _ “ WHERE [fieldGood] = “ & IIf(varBoolean, “True”, “False”) Если переменная содержит значение True, то функция вернет строковое значение “True”, и наоборот.
Передача переменной типа Date имеет свои нюансы. В конечном итоге ее тоже придется передавать как строковую переменную, только с учетом ее собственного формата. В SQL-запрос дата передается в следующем виде: #dd/mm/yy#, поэтому также нужно собрать и строковую переменную. Тут есть некоторое нюансы, которые вы должны учитывать. Во-первых, даже если в вашей операционной системе установлен формат даты с разделителем в виде точки (и соответственно, так же она отображается в полях вашей базы данных), формат передаваемой в SQL-запрос переменной все равно в качестве разделителя должен иметь слеш. Во-вторых, вы можете наткнуться на ситуацию, когда запрос с переменной в формате #dd/mm/yy# будет выводить неверные результаты. В этом случае вам необходимо воспользоваться американским форматом: #mm/dd/yy# (переставить местами месяц и день). Это приносим много неудобства, особенно, когда вы используете функции Date, Now и пр. для получения текущей даты, чтобы в последствии вставить эту переменную в SQL-запрос. Возможно, вам даже понадобится написать отдельную функцию, переводящую один формат в другой.
Dim strDate As String strDate = “#08/27/2002#” strSQL = “ SELECT * & _ “ FROM [tblOperations] “ & _ “ WHERE [fieldDate] = “ & strDate
Теперь поговорим о переменных byte, integer и long. Эти переменные необязательно обрамлять одинарными кавычками. Допустим у нас имеется база данных с несколькими таблицами, имена которых «1», «2», «3», «4», «5». Нам нужно выбрать со всех таблиц данные и разнести по разным элементам отображения информации. Создадим пять экземпляров элемента управления ListBox с одинаковым названием и индексами от 0 до 4. Вот как будет выглядеть наш код:
Dim intTable As Integer For intTable = 1 To 5 strSQL = “SELECT * & _ “ FROM “ & intTable rst.Open strSQL
Do Until rst.EOF ListBox(intTable – 1).AddItem rst.Fields(“Field1”) & “ “ & vbCrLf rst.MoveNext Loop rst.Close Next intTable
Этот код пять раз выберет набор записей и разнесет его по разным элементам управления. Тоже самое можно сделать и с именами таблицы, если в их названиях присутствуют последовательные целые числа. Если имена таблиц или полей таблицы более сложны, можно «собрать» их имя в строковую переменную, используя все ту же конкатенацию. Пусть в таблице [tbl1] имеются поля [field 1], [field 2] и [field 3]. Выборку из нее можно сделать, используя следующий код:
Dim intField As Integer Dim strField As String
For intField = 1 To 3 strField = “[field” & intField & “]” strSQL = “SELECT “ & Quote(strField) & _ “ FROM [tbl1]”
rst.Open strSQL
Do Until rst.EOF
ListBox(intTable – 1).AddItem rst.Fields(“Field1”) & “ “ & vbCrLf rst.MoveNext Loop rst.Close Next intField
Использование переменных существенно повышает гибкость программы. Вам достаточно написать одну процедуру с правильно спланированным запросом, и в последующем просто вызывать ее, передавая, как параметры переменные. Тем самым вы уменьшите свой код и повысите его «КПД» — продуктивность использования. Данный обзор далеко не исчерпывающий (как и любая тема в программировании), но я надеюсь, он достаточен для эффективного решения возникающих проблем.
27.08.2002 г.
[1] Джеффри П. Мак-Манус «Обработка баз данных на Visual Basic 6» стр. 153
Вернуться к разделу статей
butterfly-art.narod.ru
sql - SSRS - игнорировать переменные SQL в моем запросе
Всякий раз, когда вы добавляете оператор выбора в SSRS, он по умолчанию обычно добавляет ваши параметры для вас, если вы вставляете оператор select, например:
select thing from table where item = @Parm1Затем он должен отобразить на экране конструктора отчетов в разделе "Данные отчета" папку "Параметры". Если этот параметр со значением не существует, его необходимо добавить для работы вашего основного тела. Параметры выбираются по-разному в SSRS, затем в SQL. Вы определяете их в своем разделе.
Если вы хотите, чтобы он игнорировал два параметра, почему их нужно включать? Это кажется немного контр-интуитивным. У вас есть два варианта:
-
Если объявлены переменные, установите значение по умолчанию для статического значения.
-
Задайте переменную для 'allow nulls' и обработайте нулевую ссылку.
EDIT (с CTE ниже):
В SSRS Вы этого не делаете (вообще говоря, иногда вам может понадобиться переменная таблицы, и это нормально или другая статика):
Declare @Var int; select thing from table where item = @VarВы просто делаете это:
select thing from table where item = @VarЗатем вы обрабатываете параметр "Параметр", поскольку он имеет свойство с типом и детерминированными результатами.
Я бы просто сделал это в SSRS Dataset:
with dates as ( select case @Q when 1 then '1/1/' + convert(varchar(10),@Year) when 2 then '4/1/' + convert(varchar(10),@Year) when 3 then '7/1/' + convert(varchar(10),@Year) when 4 then '10/1/' + convert(varchar(10),@Year) end as StartDate , case @Q when 1 then '3/31/' + convert(varchar(10),@Year) when 2 then '6/30/' + convert(varchar(10),@Year) when 3 then '9/30/' + convert(varchar(10),@Year) when 4 then '12/31/' + convert(varchar(10),@Year) end as EndDate ) select things from mainbodytable, dates -- CTE reference where date between StartDate and EndDate -- referenced from CTE aboveУбедитесь, что вы видите параметры, перечисленные для "Q" и "Год", в папке "Параметры" и установите их в целые числа. Когда пользователь запускает отчет, он запрашивает эти значения, и они будут определять набор данных, если они являются допустимыми значениями в области. EG: они не являются недействительными значениями, которые возвращают значения null.
qaru.site
