ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Отчетные системы [игнор отключен] [закрыт для гостей] / Использование sp_executesql в SQL Server Reporting Services
25 сообщений из 32, страница 1 из 2
  1  все
Использование sp_executesql в SQL Server Reporting Services
    #33765967
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Ситуация сложилась такая, очень прошу помощи:

1) Для построение отчетов, я использую SQL Server Reporting Services - это компонент Microsoft Visual Studio 2005.

2) В этой среде в качестве входных параметров, при формировании отчета, можно выбрать такой параметр как: multi-value. Этот параметр предоставляет возможность выбирать не один (sign) параметр, а несколько, из списка возможных. На экране это выглядит как выпадающий список с несколькими заданными заранее значениями (значения эти могут быть любого типа: string, integer и тд). Записывается он в переменную, какого типа я сама не понимаю, но содержит он данные (тип данных указывается при объявлении этого multi параметра), которые мы выбираем из выпадающего списка - через запятую. То есть если я выбираю из выпадающего списка значения типа integer: 1 2 3 4 5, то выглядеть они будут так: 1,2,3,4,5 (интересно какой тип эта переменная будет иметь?)

3) При выборе такого вот параметра при выполнении отчета, и использовании его в дальнейшем в SQL скрипте возникает одна большая проблемма, заключается она в следущем: этот параметр, невозможно использовать нигде, кроме как в конструкции ESLECT в разделе WHERE ..... IN(multi-value). А мне необходимо передавать этот multi параметр в процедуру!!!

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


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

создаю процедуру:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE      PROCEDURE dbo.PRC_TLS_REP_LastSettlemen_Case
	@ParamCaseID varchar( 255 ),
	@ParamAll int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT     'Case' + ' ' + CONVERT(char( 255 ), TLC.CaseID) AS CaseID, 
                  TLC.DateLastUpdated AS Date_Committed, 
                  TLV.FinalStlValue AS Settlement_Offer, 
                  TLC.Mileage, 
                  TLV.MileageAdj
FROM         TL_Case AS TLC with (nolock)
			LEFT OUTER JOIN TL_VehicleValuation TLV 
				ON TLC.CaseID = TLV.CaseID AND TLC.VersionNo = TLV.VersionNo
WHERE    (TLC.CaseID IN (@ParamCaseID) AND  @ParamAll =  0 )  OR @ParamAll =  1

в этой процедуре параметр @ParamAll - не играет особой роли, а вот @ParamCaseID и является этим загадочным параметром, который невозможно передать в эту процедуру из за того что он выбран множественно!

Все что я смогла написать и удачно выполнить было следующее:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
DECLARE @ParamCaseID varchar( 255 ), @s nvarchar( 1000 )

SET @ParamCaseID = '1,2,3'
set @s ='SELECT TLC.CaseID FROM TL_Case AS TLC 
 	LEFT OUTER JOIN TL_VehicleValuation TLV 
 	ON TLC.CaseID = TLV.CaseID AND TLC.VersionNo = TLV.VersionNo 
 	WHERE    TLC.CaseID IN ('+@ParamCaseID+')' 
exec sp_executesql @s
Но это лишь часть того что мне нужно, в разделе WHERE я могу написать только одно условие, а у меня их три!

Может кто-то сталкивался с подобной ситуацие, ведь существует же такой параметр как multi-value а работать то как с ним, удобный но ни к чему не прикладываемый??
Помогите пожалйста :), ведь вопрос уже больше по SQL чем по репортинг сервису.
...
Рейтинг: 0 / 0
01.06.2006, 13:55
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766137
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
А зачем в этом случае Вы создаете процедуру? Помоему multi-value параметр в этом запросе и без процедуры прекрасно отработает.
...
Рейтинг: 0 / 0
01.06.2006, 14:38
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766413
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Дело в том, что у заказчика требование, все запросы к SQL Server должны выполняться только посредством хранимых процедур! К сожалению
...
Рейтинг: 0 / 0
01.06.2006, 15:52
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766509
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
BOLWriting Queries that Map to Multivalued Report Parameters
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

* The data source must be SQL Server, Oracle, or Analysis Services.

* The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.

* The query must use an IN clause to specify the parameter
...
Рейтинг: 0 / 0
01.06.2006, 16:15
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766550
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Спасибо огромное, дуреха не заметила этого в BOL!
...
Рейтинг: 0 / 0
01.06.2006, 16:25
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766559
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
pr0ger BOL...
* The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
...

But you still can do it yourself if stored procedures the only way to go : Массивы и Списки в SQL Server
...
Рейтинг: 0 / 0
01.06.2006, 16:28
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766581
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
BugsBunny pr0ger BOL...
* The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
...

But you still can do it yourself if stored procedures the only way to go : Массивы и Списки в SQL Server
Более проблематично третье условие The query must use an IN clause to specify the parameter
...
Рейтинг: 0 / 0
01.06.2006, 16:32
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766629
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
В смысле более проблематично?
...
Рейтинг: 0 / 0
01.06.2006, 16:40
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766642
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
Где взять IN в параметрах хранимой процедуры?
...
Рейтинг: 0 / 0
01.06.2006, 16:42
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766689
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
2 pr0ger : What I meant was "If you start using sp - you are on your own and no more restriction like mentioned "The query must use an IN clause to specify the parameter" are applied". So, it was just a link to the knowledge "How to be on one's own with multivalued parameters".
As per reporting services, it's exactly what they do: just replace "multivalued" parameter in the query with "union all" subquery - the same dynamic SQL byverka used. No doubt that it will be quite tricky to do the same with stored procedure (especially if it's encrypted) :)

By the way, Oracle's row-level security did exactly the same (on engine level, of course) : replaced "row-level security"-enabled table with corresponding view in all stemenets. Side-effects: sounds great but good for small tables only. (disclaimer: my knowledge of Oracle is theoretical only)
...
Рейтинг: 0 / 0
01.06.2006, 16:54
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33766840
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
Вот что получилось:
В процедуру передается строка с параметрами через запятую, процедура их обрабатывает как описано в вышепреведенной статье
В отчете Dataset выглядит примерно так:
Код: plaintext
1.
2.
declare @res varchar( 8000 )
select @res=isnull(@res,'')+case when @res='' then '' else ', ' end +convert(varchar( 20 ), id) from mytable where id in (@multivalue )
exec test_proc @res
...
Рейтинг: 0 / 0
01.06.2006, 17:26
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767041
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
pr0gerВот что получилось:
В процедуру передается строка с параметрами через запятую, процедура их обрабатывает как описано в вышепреведенной статье

"Reporting Service"-compatible wrapper - really neat idea of using RS "pre-processor" to handle multivalued parameters.
Can it handle following approach (picked up from some third-party reporting tool)?
1. load multivalued parameters into "parameters" table
2. use "parameters" table inside stored procedure filtering output with "inner join"
3. clean up
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
--1.
declare @session uniqueidentifier; set @session = newsequentialid();
insert into [parameters] (session,name,value)
select @uid,'parameter1',id from mytable1 where id in (@multivalue1)
--union all select @uid,'parameter2',id from mytable2 where id in (@multivalue2)
--..
--2.
exec test_proc @session;
--3.
delete [parameters] where session = @session;
...
Рейтинг: 0 / 0
01.06.2006, 18:14
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767072
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
pr0gerВот что получилось:
В процедуру передается строка с параметрами через запятую, процедура их обрабатывает как описано в вышепреведенной статье
В отчете Dataset выглядит примерно так:
Код: plaintext
1.
2.
declare @res varchar( 8000 )
select @res=isnull(@res,'')+case when @res='' then '' else ', ' end +convert(varchar( 20 ), id) from mytable where id in (@multivalue )
exec test_proc @res


В вышеприведенной статье, описана функция, в которую передается переменная типа ntext, а у меня вообще непонятно какого типа переменная, причем еще и не распарсеная. Проблемма у меня именно в этом, как передать этот параметр в функцию??
...
Рейтинг: 0 / 0
01.06.2006, 18:22
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767268
Игорь Оробчук
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Игорь Оробчук
Участник
byverkaВ вышеприведенной статье, описана функция, в которую передается переменная типа ntext, а у меня вообще непонятно какого типа переменная, причем еще и не распарсеная. Проблемма у меня именно в этом, как передать этот параметр в функцию??
ntext просто содержит значиния любого типа. А сама функция уже должна знать как правильно сделать convert.
...
Рейтинг: 0 / 0
01.06.2006, 19:36
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767291
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Игорь Оробчук byverkaВ вышеприведенной статье, описана функция, в которую передается переменная типа ntext, а у меня вообще непонятно какого типа переменная, причем еще и не распарсеная. Проблемма у меня именно в этом, как передать этот параметр в функцию??
ntext просто содержит значиния любого типа. А сама функция уже должна знать как правильно сделать convert.

Не получается, делаю так:
Создаю в своей базе функцию -
duo_text_split_me(@list ntext, @delim nchar(1) = N',') - точно так же как описано в статье!

в самом дата сете пишу следующее:
select * from duo_text_split_me(@ParamCaseID,DEFAULT) - что бы просто посмотреть, схватит ли функция этот параметр.
Делаю Preview отчета, выдается следующая ошибка:
Код: plaintext
Procedure or Function dbo.duo_text_split_me has too many aeguments sprcified
То есть это говорит о том, что даже ntext не принимает эту переменную, просто, видимо, потому что она в таком формате странном находиться, что распарсить ее человеческим методом невозможно.. По крайней мере пока у меня не получилось никак. Беда какая-то..
...
Рейтинг: 0 / 0
01.06.2006, 19:48
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767372
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
Эта функция используется для преобразования строки вида 'param1, param2, param3' в табличный вид, т.е. она должна использоваться уже в процедуре, а не в датасете.
Для преобразования multivalue-параметра в строку можно использовать способ, который я описал ранее. Или сделать, как описал BugsBunny, с помощью вспомогательной таблицы с параметрами, это более производительный и более масштабируемый способ.
...
Рейтинг: 0 / 0
01.06.2006, 21:18
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33767447
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
byverka...Делаю Preview отчета, выдается следующая ошибка:
Код: plaintext
Procedure or Function dbo.duo_text_split_me has too many aeguments sprcified
...
Can you run profiler to see what statement(s) are sent to the server by tracing SQL:Batch Starting or/and SQL:Statement Starting events?
...
Рейтинг: 0 / 0
01.06.2006, 22:57
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769645
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Ребят, всем спасибо огромное, но все равно без SELECT а здесь никак не обойтись, то есть что бы хоть как-то распарсить параметр multi. Потом получилась такая картина:

1) Создала процедуру:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
ALTER        PROCEDURE dbo.PRC_TLS_REP_LastSettlemen_Case
	@ParamCaseID varchar( 8000 ),
	@ParamAll int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @s nvarchar( 1000 ), @var char( 4 )

SET @var = 'Case'

set @s ='SELECT @varc + CONVERT(char(255), TLC.CaseID) AS CaseID, 
                  TLC.DateLastUpdated AS Date_Committed, 
                  TLV.FinalStlValue AS Settlement_Offer, 
                  TLC.Mileage, 
                  TLV.MileageAdj 
	 FROM TL_Case AS TLC 
			LEFT OUTER JOIN TL_VehicleValuation TLV 
 	 			ON TLC.CaseID = TLV.CaseID AND 
				   TLC.VersionNo = TLV.VersionNo
 	WHERE    (TLC.CaseID IN ('+@ParamCaseID+') AND @CAll = 0)  OR @PAll = 1' 

exec sp_executesql @s, N'@varc char(4),@CAll int, @PAll int',
			 @var, @ParamAll, @ParamAll

Далее идем в мой дата сет, вот как он выглядит:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
DECLARE @res varchar( 8000 )

SELECT @res =  ISNULL(@res,- 1 ) + 
                           CASE  WHEN @res ='' THEN '' 
                                      ELSE ', ' 
                           END +

                          MAX(CONVERT(varchar( 20 ), CaseID)) 
                           FROM dbo.TL_Case 
                           WHERE CaseID IN (@ParamCaseID) 
                           GROUP BY CaseID 
                          
exec dbo.PRC_TLS_REP_LastSettlemen_Case @res,  0

Ругаеться:

Код: plaintext
Syntax error converting the varchar value '1,2,3' to a column of data type int.

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

Код: plaintext
exec dbo.PRC_TLS_REP_LastSettlemen_Case '1,2,3',  0

Да, хотела отметить важный момент - поле CaseID в таблице TL_Case - типа int! Из за этого у меня все проблеммы!

Все остальное не проходит, в общем сложно, а в profiler не могу посмотреть какой параметр передается в процедуру, не могу найти в настройках profiler то событие, которое бы выводил бы значение передаваемого в процедуру параметра. Может быть подскажете как это событие в profiler называется?
...
Рейтинг: 0 / 0
02.06.2006, 17:19
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769738
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
Did you try method suggested by pr0ger? I think it should work.
It seems like Microsoft just wrap your select query into dynamic sql by replacing @multivalue with "1,2,3" (without quotes)
...
Рейтинг: 0 / 0
02.06.2006, 17:44
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769800
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
BugsBunnyDid you try method suggested by pr0ger? I think it should work.
It seems like Microsoft just wrap your select query into dynamic sql by replacing @multivalue with "1,2,3" (without quotes)

В каком именно вызове я должна использовать параметр @multivalue без кавычек??
...
Рейтинг: 0 / 0
02.06.2006, 18:00
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769813
Фотография byverka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
byverka
Участник
Дело все лишь в том, что все запросы к серверу должны быть обернуты в хранимые процедуры, в моем случае это сделать никак не получается, по крайней мере последний вариант моей процедуры и дата сета - не работают, если честно я не знаю почему.
...
Рейтинг: 0 / 0
02.06.2006, 18:03
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769815
tacit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tacit
Гость
Сам недавно столкнулся с такой проблемой. Решил по "рабоче-крестьянски". В запросе (DataSet) передаю в процедуру параметр типа varchar. Например, @MultiValueString. Сам параметр не декларируется в запросе. RS designer при этом создает параметр отчета MultiValueString. Делаем его скрытым (hiden) и присваиваем ему дефолтое значение через функцию Join(...), которая выпрямляет выбранные значения множественного параметра в одну строку c указанным разделителем. И все. Естественно, в процедуре используется динамический запрос (exec sp_executesql @cmd).
...
Рейтинг: 0 / 0
02.06.2006, 18:04
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769829
pr0ger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pr0ger
Участник
а без динамического SQL? что-то вроде:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE      PROCEDURE dbo.PRC_TLS_REP_LastSettlemen_Case
	@ParamCaseID varchar( 8000 ),
	@ParamAll int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT     'Case' + ' ' + CONVERT(char( 255 ), TLC.CaseID) AS CaseID, 
                  TLC.DateLastUpdated AS Date_Committed, 
                  TLV.FinalStlValue AS Settlement_Offer, 
                  TLC.Mileage, 
                  TLV.MileageAdj
FROM         TL_Case AS TLC with (nolock)
			LEFT OUTER JOIN TL_VehicleValuation TLV 
				ON TLC.CaseID = TLV.CaseID AND TLC.VersionNo = TLV.VersionNo
WHERE    (TLC.CaseID IN (select * from dbo.duo_text_split_me(@ParamCaseID,',')) AND  @ParamAll =  0 )  OR @ParamAll =  1
...
Рейтинг: 0 / 0
02.06.2006, 18:08
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769836
tacit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tacit
Гость
pr0gerа без динамического SQL? что-то вроде:
Сори, у меня еще и динамический источник данных (таблицы разные). Токо из-за этого
...
Рейтинг: 0 / 0
02.06.2006, 18:11
| Ответить | Цитировать | Написать  
Использование sp_executesql в SQL Server Reporting Services
    #33769870
Фотография BugsBunny
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
BugsBunny
Участник
to byverka: I didn't get it - did you tried this:

create procedure for your working version of dynamic sql:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create procedure dbo.PRC_TLS_REP_LastSettlemen_Case_test
	@CaseID_List varchar( 8000 )
as
set nocount on
declare @s nvarchar( 4000 )
set @s ='SELECT TLC.CaseID FROM TL_Case AS TLC 
 	LEFT OUTER JOIN TL_VehicleValuation TLV 
 	ON TLC.CaseID = TLV.CaseID AND TLC.VersionNo = TLV.VersionNo 
 	WHERE    TLC.CaseID IN ('+@CaseID_List+')' 
exec sp_executesql @s
return
go

in report
Код: plaintext
1.
2.
3.
set nocount on
declare @CaseID_List varchar( 8000 )
select @CaseID_List = isnull(@CaseID_List + ',','') + convert(varchar( 20 ), CaseID) from TL_Case where CaseID in (@multivalue )
exec dbo.PRC_TLS_REP_LastSettlemen_Case_test @CaseID_List = @CaseID_List
...
Рейтинг: 0 / 0
02.06.2006, 18:22
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / Отчетные системы [игнор отключен] [закрыт для гостей] / Использование sp_executesql в SQL Server Reporting Services
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]