Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
Всем привет.
select @@version
Используется SQL 2012 + Report Server 2012
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Как получить коллекцию полей возвращаемых хранимой процедурой которая использует в своем теле ВРЕМЕННЫЕ ТАБЛИЦЫ.
Задача усложняется тем что хранимая процедура(набор полей которой я хочу получить) в своем теле использует ВРЕМЕННЫЕ ТАБЛИЦЫ и многие методы которые предлагаются не подходят для получения набора (коллекции полей) из этой хранимой процедуры.
Гуглил, искал, но к сожалению однозначного решения так и не нашел. Да есть всяко разные предложения как то How to Share Data between Stored Procedures или
Using OPENROWSET to dynamically retrieve SP results when SP contains # temp tables
. Единственное я не пробовал из этой статьи только ‘bcp queryout’ потому как на мой взгляд это совсем ‘топорно’.
Т.е. к примеру имеем процедуру repIndexPaymentTesting при выполнении из консоли MS команды
exec repIndexPaymentTesting @PerionBegin=NULL,@PerionEnd=NULL, @SitesList=NULL, @CountriesList=NULL, @RegTypesList =NULL

как результат данной команды в консоли виден заголовок с перечнем полей которые возвращает данная процедура и строк здесь 0. Но вот как мне получить доступ к коллекции(набору) вот такого пустого резалтсета где есть перечень полей. Мне надо получить наименование этих колонок (возвращаемых этой процедурой) и потом слепить через запятую в строку.
Пытался делать так:
SELECT * FROM OPENQUERY (LocalServer,'EXEC repIndexPaymentTesting @PerionBegin=NULL,@PerionEnd=NULL, @SitesList=NULL, @CountriesList=NULL, @RegTypesList =NULL')

Ругается
автор
«The metadata could not be determined because statement 'INSERT #rl
SELECT Cast(row_value AS INT)
FROM Fnstrlisttotable(',', @RegTypesList)' in procedure 'repIndexPaymentTesting' uses a temp table.»


Пытался так
SET FMTONLY ON;
exec repIndexPaymentTesting @PerionBegin=NULL,@PerionEnd=NULL, @SitesList=NULL, @CountriesList=NULL, @RegTypesList =NULL
SET FMTONLY OFF

Ругается
автор
«Msg 208, Level 16, State 0, Procedure repIndexPaymentTesting, Line 175
Invalid object name '#ssl'.»

В 2012 можно использовать процедуру sp_describe_first_result_set
EXEC sp_describe_first_result_set N'repIndexPaymentTesting'

Ругается примерно так
автор
« error_message The metadata could not be determined because statement 'INSERT #rl
SELECT Cast(row_value AS INT)
FROM Fnstrlisttotable(',', @RegTypesList)' in procedure 'repIndexPaymentTesting' uses a temp table., error_type_desc TEMPORARY_TABLE, »

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('repIndexPaymentTesting'), 1) 

Ругается примерно так же как на примере выше.
Все сводится к тому что использование временных таблиц в процедуре сильно осложняет получение перечня полей которые возвращает эта самая процедура. Но ведь как то же движек получает этот набор полей из процедуры?! Если кто знает поделитесь методом как получить набор полей возвращаемых хранимой процедурой которая в свою очередь в своем теле использует ВРЕМЕННЫЕ ТАБЛИЦЫ для промежуточных расчетов.
Либо может кто то подскажет как можно в отчете RS при помощи или пользовательского кода или еще какой то 'магии' получить строкой через разделитель перечень полей одного из DataSetsов которые фигурируют в отчете. Т.е. существует DataSet с именем repIndexPaymentTesting у которого в качестве процедуры которая получает данные фигурирует наша хранимка repIndexPaymentTesting и в дереве метаданных в самом RS в окне Report Data в ветке Datasets мы видим repIndexPaymentTesting и в ней поля возвращаемые этой самой процедурой.
Так вот вопрос. Как можно получить набор полей нашего Datasets.? А я уже потом сам пройдусь по коллекции набора полей и склею в строку через запятую и помещу эту строку в текстовую переменную отчета.
Смысл всех этих 'страданий' сводится к тому чтоб попробовать 'запилить' возможность изменения порядка группировок в отчете. Аналогичная тема подымалась вот здесь. Параметры и группы в Reporting Services но это было 100 лет назад и ничего конкретного.
автор
Можно ли изменять порядок группировок в отчете, отключать группировки по выбору пользователя ? т.е., для примера, нужно получить остатки товаров по складам и по группам. По желанию пользователь должен иметь возможность получить остатки в разрезе склад-группы, или группа-склады, а может отключить одну из этих группировок (скажем группировку по группам товаров) и получить остатки по складам без разбиения по группам.
ВОТ ЧЕГО Я ХОТЕЛ БЫ ДОБИТЬСЯ В КОНЕЧНОМ ИТОГЕ. Хочется сделать как в 1сных отчетах V8. Т.е. убирать или добавлять нужную детализацию по группировкам или МЕНЯТЬ ПОРЯДОК ГРУППИРОВОК путем изменения параметров отчета в рамках одного отчета.
Но для того чтоб по пробовать динамически менять порядок группировок в отчете для начала надо знать полный набор полей которые фигурируют в отчете. Как то так.
Надеюсь у меня получилось объяснить суть моей проблемы.
Буду рад любому совету и идеи как это дело разрулить. Заранее благодарен.
26 мар 13, 12:50    [14096307]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Добавьте в свою процедуру параметер @schema_only и возвращайте пустой рекордсет, если он выставлен.
26 мар 13, 13:09    [14096424]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
Гавриленко Сергей Алексеевич
Добавьте в свою процедуру параметер @schema_only и возвращайте пустой рекордсет, если он выставлен.
26 мар 13, 13:34    [14096627]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
savs23,

Если я вас правильно понял вы предлагаете сделать как то так.

CREATE PROCEDURE dbo.zzTempSP (@A INT, @B INT, @SCHEMA_ONLY INT) AS 

IF @SCHEMA_ONLY ! = 1 
  BEGIN
   CREATE TABLE dbo.#T (A INT, B INT)
   INSERT INTO dbo.#T SELECT @A AS A, @B AS B
   SELECT * FROM dbo.#T
  END
ELSE 
   SELECT '' A, '' B


Но ведь же сама платформа (сам движек) как то умудряется получать список полей из глубин метаданных при выполнении той же команды

exec repIndexPaymentTesting @PerionBegin=NULL,@PerionEnd=NULL, @SitesList=NULL, @CountriesList=NULL, @RegTypesList =NULL
26 мар 13, 13:49    [14096740]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
savs23
Но ведь же сама платформа (сам движек) как то умудряется получать список полей из глубин метаданных при выполнении той же команды

exec repIndexPaymentTesting @PerionBegin=NULL,@PerionEnd=NULL, @SitesList=NULL, @CountriesList=NULL, @RegTypesList =NULL
Это не метаданные из глубин, это просто пустой рекордсет, который вернула ваша процедура. В рекордсете, который уходит в клиентское приложение, есть описание его структуры.
26 мар 13, 14:09    [14096849]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Glory
Member

Откуда:
Сообщений: 104751
savs23
Но ведь же сама платформа (сам движек) как то умудряется получать список полей из глубин метаданных при выполнении той же команды

Вы правильно заметили, что именно при _выполнении_.
А без выполнения - падает. Что вы видели на примере SET FMTONLY
26 мар 13, 14:13    [14096877]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
lapposv
Member

Откуда: Санкт-Петербург
Сообщений: 27
Попробуйте в процедуре поставить
set nocount on

во всяком случае в Integration это помогает при работе с хранимками и временными таблицами.
27 мар 13, 15:17    [14102312]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
Как я понял народ часто густо сталкивается с подобной проблемой как у меня. Когда есть SP использующая в своем теле времянки, а структуру возвращаемого резалт сета очень нужно знать, но штатными средствами не докапаться … тогда на помощь приходит csharp.
Все вот эти танцы с бубном SET FMTONLY ON, SET NOCOUNT ON итд. В моем случае не помогли. Не с нашим счастьем.

И так по порядку.

Пишим скрипт

using  System;
using  System.Data;
using  System.Data.SqlClient;
using  Microsoft.SqlServer.Server;


namespace  clrTest
{
     public  partial   class  clrGetSpSchemaOnly
    {

        [SqlProcedure]
         public  static  void  GetSpSchemaOnly(  string   sp_Name)
            
        {
             try
            {
                 using  (SqlConnection conn =  new  SqlConnection(  "context connection=true"))
                 using  (SqlCommand sqlCommand =  new  SqlCommand(sp_Name, conn))
              

                {
                    conn.Open();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                     using  (SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                         string  output =  ""  ;
                         for  ( int  i = 0; i <= reader.FieldCount - 1; i++)
                        {
                            SqlContext.Pipe.Send(reader.GetName(i));
                            output = output +  ", "   +reader.GetName(i);
                        }
                        SqlContext.Pipe.Send(output);
                    }
                }
            }
             catch  ( Exception  ex)
            {
              
                SqlContext.Pipe.Send(  string   .Format("Ошибка выполнения хранимой процедуры (0)\n{1}"  , sp_Name, ex.Message));
            }
          
        }
    }
}


Компилим DLL
csc /t:library /out:"C:\Users\a \Documents\DTS projects\GetSpSchemaOnly.dll" "C:\Users\a \Documents\DTS projects\GetSpSchemaOnly.cs"


Далее прикручиваем эту сборку.

CREATE ASSEMBLY GetSpSchemaOnly FROM 'C:\Users\a\Documents\DTS projects\GetSpSchemaOnly.dll'


Затем создаем процедуру которая будет использовать нашу только что зарегистрированную ASSEMBLY GetSpSchemaOnly.

CREATE PROCEDURE GetSpSchemaOnly ( @sp_Name sysname )
AS EXTERNAL NAME [GetSpSchemaOnly] .[clrTest.clrGetSpSchemaOnly] . [GetSpSchemaOnly]
GO


Запускаем

EXEC GetSpSchemaOnly repIndexPaymentTesting


Возвращает наименования полей. То что мне нужно!

period
SiteName
Counties
X
Y
Z

, period, SiteName, Counties, X, Y, Z


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

Сборка возвращает как каждое название поля по отдельности (SqlContext.Pipe.Send(reader.GetName(i));) так и строку с разделителем ',' ( SqlContext.Pipe.Send(output) ) нужное оставить на выбор.

Что касается этого
автор
ВОТ ЧЕГО Я ХОТЕЛ БЫ ДОБИТЬСЯ В КОНЕЧНОМ ИТОГЕ. Хочется сделать как в 1сных отчетах V8. Т.е. убирать или добавлять нужную детализацию по группировкам или МЕНЯТЬ ПОРЯДОК ГРУППИРОВОК путем изменения параметров отчета в рамках одного отчета.

То на буржуйских сайтах и была найдена подсказка.
Creating Dynamic Grouping Which Enables a User to Select the Fields in a Report и тут How to Set Up Dynamic Grouping in SSRS

В общем как то так.
Буду рад если мои 'изыскания' помогут кому то. :-)
3 апр 13, 11:26    [14129973]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Glory
Member

Откуда:
Сообщений: 104751
savs23
Возвращает наименования полей. То что мне нужно!

А то, что процедура выполняется при этом - это просто побочный эффект ???
И не важно, сколько она будет выполняться и что сделает ?
3 апр 13, 11:33    [14130010]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
Glory
savs23
Возвращает наименования полей. То что мне нужно!

А то, что процедура выполняется при этом - это просто побочный эффект ???
И не важно, сколько она будет выполняться и что сделает ?


Когда в самом RS выбираешь в DataSet Properties дабы получить набор полей из набора данных (в моем случае это SP). Так вот, после нажатия кнопочки refresh fields и указания для всех параметров = Null как я понимаю глядя в профайлер наша процедура ТОЖЕ запускается из под RS cо всеми параметрами = Null и она моментально выполняется и после этого выполнения (как я предполагаю) подобно данной сборки он(RS) выковыривает из пустого набора данных названия полей. А как еще без выполнения процедуры которая в своем теле использует временные таблицы, получить набор полей ею возвращаемых ? Тем более я знаю, что с Nullивыми параметрами моя процедура выполнится быстро. Как я предполагаю что и в самом RS заложен подобный механизм получения названия полей из набора данных. RS так же само пытается выполнить нужную нам SP и потом уже получает из результата, набор полей.
Как то так.
3 апр 13, 12:15    [14130304]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Glory
Member

Откуда:
Сообщений: 104751
savs23
А как еще без выполнения процедуры которая в своем теле использует временные таблицы, получить набор полей ею возвращаемых ?

Писать такие процедуры, которые умеют возвращать метаданные.
3 апр 13, 12:17    [14130317]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
Glory
Member

Откуда:
Сообщений: 104751
savs23
наша процедура ТОЖЕ запускается из под RS cо всеми параметрами = Null и она моментально выполняется и после этого выполнения (как я предполагаю) подобно данной сборки он(RS) выковыривает из пустого набора данных названия полей.

Поэтому для вызова из TSQL процедуры со всеми входными параметрами NULL вы написали обертку на С ???
3 апр 13, 12:20    [14130334]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
Glory,

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


автор
Добавьте в свою процедуру параметер @schema_only и возвращайте пустой рекордсет, если он выставлен.


Т.е. как то так?
CREATE PROCEDURE dbo.zzTempSP (@A INT, @B INT, @SCHEMA_ONLY INT) AS 

IF @SCHEMA_ONLY ! = 1 
  BEGIN
   CREATE TABLE dbo.#T (A INT, B INT)
   INSERT INTO dbo.#T SELECT @A AS A, @B AS B
   SELECT * FROM dbo.#T
  END
ELSE 
   SELECT '' A, '' B


Я правильно в примере написал подобие некой процедуры которая может возвращать если не метаданные то хотя бы названия нужных мне полей. Это должно быть как то так реализовано?
3 апр 13, 12:46    [14130577]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
savs23
Тем более я знаю, что с Nullивыми параметрами моя процедура выполнится быстро
А завтра перестанет. Или потребуются метаданные другой процедуры, возвращающей 100500 строк? Какой смысл писать разовый говнокод, что бы поставить костыль?

Если в процедуре создается временная таблица, а потом из нее возвращается результирующий набор, то получить метаданные в режиме fmtonly on не получится. Лечится использованием табличной переменной вместо временной таблицы.
3 апр 13, 12:47    [14130585]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
savs23
Member

Откуда:
Сообщений: 11
invm
savs23
Тем более я знаю, что с Nullивыми параметрами моя процедура выполнится быстро
А завтра перестанет. Или потребуются метаданные другой процедуры, возвращающей 100500 строк? Какой смысл писать разовый говнокод, что бы поставить костыль?

Если в процедуре создается временная таблица, а потом из нее возвращается результирующий набор, то получить метаданные в режиме fmtonly on не получится. Лечится использованием табличной переменной вместо временной таблицы.

Кстати, да. Надо будет по пробовать. Если это поможет, буду благодарен.
3 апр 13, 13:42    [14131013]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8879
invm
savs23
Тем более я знаю, что с Nullивыми параметрами моя процедура выполнится быстро
А завтра перестанет. Или потребуются метаданные другой процедуры, возвращающей 100500 строк? Какой смысл писать разовый говнокод, что бы поставить костыль?

Если в процедуре создается временная таблица, а потом из нее возвращается результирующий набор, то получить метаданные в режиме fmtonly on не получится. Лечится использованием табличной переменной вместо временной таблицы.

Эмммм... Пример, пожалуйста, в студию...
4 апр 13, 01:12    [14133452]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
SIMPLicity_
Эмммм... Пример, пожалуйста, в студию...
use tempdb;
go

create procedure dbo.TestProc1
as
begin
 set nocount on;
 
 create table #t (i int);
 select * from #t;
end;
go

create procedure dbo.TestProc2
as
begin
 set nocount on;
 
 declare @t table (i int);
 select * from @t;
end;
go

select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.TestProc1;');
go

select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.TestProc2;');
go

drop procedure dbo.TestProc1, dbo.TestProc2;
go
4 апр 13, 09:27    [14133839]     Ответить | Цитировать Сообщить модератору
 Re: Как получить набор полей возвращаемых SP в теле которой есть ВРЕМЕННЫЕ ТАБЛИЦЫ.  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8879
invm
SIMPLicity_
Эмммм... Пример, пожалуйста, в студию...
use tempdb;
go

create procedure dbo.TestProc1
as
begin
 set nocount on;
 
 create table #t (i int);
 select * from #t;
end;
go

create procedure dbo.TestProc2
as
begin
 set nocount on;
 
 declare @t table (i int);
 select * from @t;
end;
go

select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.TestProc1;');
go

select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.TestProc2;');
go

drop procedure dbo.TestProc1, dbo.TestProc2;
go


Спасибо за пример! Теперь я понял смысл фразы:

invm
Если в процедуре создается временная таблица, а потом из нее возвращается результирующий набор, то получить метаданные в режиме fmtonly on не получится. Лечится использованием табличной переменной вместо временной таблицы
...
А я было подумал про какой-нибудь хитрый ход с использованием передаваемых и возвращаемых табличных переменных. Или с возвращаемым неопределённым типом...
4 апр 13, 11:15    [14134501]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить