Возврат результата из хранимой процедуры

Видимо нужно указать Parameters.ItemsByName('isEnought').ParamType = 1.

Так как у IParameter свойство ParamType: InputOutputParamTypeEnum по-умолчанию имеет значение 0, т.е. входящий:

enum _InputOutputParamTypeEnum {
		ioptInput = 0,
		ioptOutput = 1,
		ioptInputOutput = 2
	} InputOutputParamTypeEnum;

Большое спасибо, Александр!!!

Действительно, пропустил указать тип параметра.
Теперь работает:

var Parameters = CreateSPParameters();
CreateSPParameter(Parameters, 'InvoiceID', pdtGUID, InvoiceID);
CreateSPParameter(Parameters, 'StoreID', pdtGUID, StoreID); 
CreateSPParameter(Parameters, 'isEnought', pdtInteger, 0);
Parameters.ItemsByName('isEnought').ParamType = 1; // It!
 
var SQLText = 'exec dbo.tsp_IsEnoughtOfferingInStore :InvoiceID, :StoreID, :isEnought OUTPUT';
Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);
 
var IsEnought = Parameters.ItemsByName('isEnought').ValAsInt;

--
Cogito, ergo sum

Рано тему закрывать! Новая проблема :)

При тестирование на обычной (MS SQL) конфигурации выходной параметр заполняется, но, при тестирование через WebServices - параметр НЕ заполняется!

В чем фишка?

--
Cogito, ergo sum

Думаю что фишка в ядре. Скорее всего вебсервисы просто не учитывают тот факт что параметр может возвращаться. Может как-то переделать на использование запроса с кастомной колонкой?

Разработчики пообещали сделать сборку 3.3.0.51, где будет исправлена эта ошибка... Ждем-с-с...

--
Cogito, ergo sum

Сегодня добавил для Web сервисов возможность возвращать результаты ExecuteCustomSQL через Output параметры. Обращайтесь в поддержку за сборкой 3.3.0.51.

Александр, большое спасибо!

--
Cogito, ergo sum

Я так понимаю, что описанный здесь способ вернуть результат работы хранимой процедуры в версии 3.0.2.244 не предусмотрен.
Как вернуть результат?

Посмотрите здесь и дальше по комментариям - способ возвращения через SelectQuery с использованием CustomSQL-колонок. При этом в конце хранимой процедуры должен быть Select, который возвращает необходимый результат.

а можно подробней? не совсем понятен принцип... каким образом получить результат из сервиса SelectQuery? в конце ХП создал select, а как то что он возвращает увязать с SelectQuery?
там еще рекомендуют создать поле (Result), но как его создать, если выбор возможен только из полей таблицы из секции FROM?

Результат Вы получаете в колонки запроса. Например, если в конце ХП Вы выполняете такой запрос:

select Field1, Field2, Field3 from tbl_Table
...

то в самом сервисе SelectQuery необходимо создать CustomSQL-колонки с названиями Field1, Field2, Field3. Причём в свойство "Текст SQL" первой из них Вам необходимо вставить вызов ХП:

1 as [a] into #tmp
drop table #tmp
 
EXEC ProcedureName :ParamName
 
/*

А для таблицы блока FROM сервиса запроса необходимо прописать алиас "*/--" (без двойных кавычек). Это необходимо, чтобы закомментировать остальной текст запроса.

Далее, после получения экземпляра этого SelectQuery и установки значений необходимых параметров результат выполнения ХП можно так: var Dataset = SelectQuery.Open();

Поля датасета будут содержать результат выполнения.

Что касается совета Дениса:

"в конце процедуры напишите что-то типа
select 1 as [Result]
а в SelectQuery создайте поле
Result - целого типа"

это частный случай того, что я написал выше (если хранимая процедура не возвращает ничего, "заставляем" её вернуть фиктивный результат в колонку запроса).

"SSV" написал:там еще рекомендуют создать поле (Result), но как его создать, если выбор возможен только из полей таблицы из секции FROM?

Именно поэтому рекомендуется создавать CustomSQL-колонки. В них не обязательно выбирать поля из таблицы блока FROM. Более того, в этом запросе абсолютно все колонки должны быть типа CustomSQL, а таблица желательно такая, которая содержит одну строку (чтобы ХП не выполнялась столько раз, сколько записей в таблице). Поэтому, поскольку таблица tbl_DatabaseInfo всегда должна содержать только одну запись, и мы не собираемся ни выбирать из неё информацию, ни выполнять над ней никаких операций, используется именно эта таблица.

Загрузите сервис из примера, и Вы увидите, что он удовлетворяет всем описанным требованиям.

в конце процедуры у меня такой селект:

select @ContractNewID as Result

в SelectQuery я вставил CustomSQL-поле с именем Result, в поле "Текст SQL" что-нибудь нужно писать? ибо всё вроде бы и работает, но в Result ничего не передается...

Свойство "Текст SQL" первой из колонок необходимо заполнить текстом:

1 AS [a] INTO #tmp
DROP TABLE #tmp
 
EXEC ProcedureName :ParamName
 
/*

В Вашем случае колонка единственная - её свойство "Текст SQL" так же необходимо заполнить. Вместо ProcedureName необходимо указать название Вашей процедуры, далее, если необходимо, - входящие параметры.

вот вложил созданный сервис. далее текст из скрипта:

SQ_SP = Services.GetNewItemByUSI('sq_ResultSP');
SQ_SP.Parameters.ItemsByName('ContractOldID').Value = Dataset.ValAsStr('ContractID');
SQ_SP.Parameters.ItemsByName('NewAccountID').Value = Dataset.ValAsStr('AccountToID');
SQ_SP.Parameters.ItemsByName('CertificateDate').Value = Dataset.ValAsDateTime('CertificateDate');
var DatasetResult = SQ_SP.Open();
var IDNewContract = DatasetResult.ValAsGUID('Result');

в IDNewContract возвращается пустая строка...:sad:

Попробуйте запустить профайлер перед выполнением SQ_SP.Open() и посмотреть, какой запрос он возвращает. Потом выполните этот запрос с помощью Query Analyzer или SQL Server Management Studio и посмотрите на результат, а также на значения параметров, которые передаются в процедуру. Возможно, это подскажет дальнейшее направление анализа проблемы.

вот что показывает профайлер:

exec sp_executesql N'SELECT
	1 AS [a] INTO #tmp
drop table #tmp
 
EXEC spChangeLegalPerson_AddNewContract @P1, @P2, @P3
/* AS [Result]
FROM
	[tbl_DatabaseInfo] AS [*/--]',N'@P1 varchar(38),@P2 varchar(38),@P3 datetime','{461DA3E2-8F2D-488B-A175-1F17356ECFAD}','{69D74C53-113C-493A-8F1A-7C6733B58FCE}',''2010-11-03 00:00:00:000''

т.е. передается всё что нужно...

А результат выполнения этого запроса в Management Studio есть?

хм... вот только что-то непонятно как это передается дата... но ошибок не выдает, всё отрабатывает правильно...

да, в Result возвращается GUID...
но - в том виде в котором этот запрос был скопирован из профайлера, он не выполняется, если убрать у даты лишние кавычки, то всё проходит на ура...

Попробуйте при установке параметров использовать Values:

SQ_SP = Services.GetNewItemByUSI('sq_ResultSP');
SQ_SP.Parameters.ItemsByName('ContractOldID').Value = Dataset.Values('ContractID');
SQ_SP.Parameters.ItemsByName('NewAccountID').Value = Dataset.Values('AccountToID');
SQ_SP.Parameters.ItemsByName('CertificateDate').Value = Dataset.Values('CertificateDate');
var DatasetResult = SQ_SP.Open();
var IDNewContract = DatasetResult.ValAsGUID('Result');

Если дата будет не в нужном формате, можно в тексте SQL колонки запроса привести её к нужному типу:

1 as [a] into #tmp drop table #tmp  EXEC spChangeLegalPerson_AddNewContract :ContractOldID, :NewAccountID, cast(:CertificateDate as datetime) /*

Также мне кажется, что поскольку значения параметров берутся из одного и того же датасета, достаточно будет в хранимку передавать значение поля ID, а остальные значения непосредственно получать в процедуре. Количество запросов от этого не должно измениться (тот же Select, который Вы выполняете для открытия датасета Dataset, будет выполняться в хранимой процедуре).

пробовал использовать Values, в профайлере запрос имеет тот же вид, но при этом выполняется без проблем... попытка привести параметр к типу datetime порождает ошибку - что-то о неправильном параметре... результат в поле Result процедура не возвращает... может и такого способа моя версия не поддерживает? а ADO позволяет это сделать?

Да, ADO должен позволять даже в 3.0.2.

Но возможно, быстрее будет переписать хранимую процедуру, как я писал выше:

"Лабьяк Олег Игоревич" написал:поскольку значения параметров берутся из одного и того же датасета, достаточно будет в хранимку передавать значение поля ID, а остальные значения непосредственно получать в процедуре.

Насколько я понимаю, проблема только в параметре типа datetime, а с параметром ID типа uniqueidentifier проблем возникнуть не должно.

проблем с datetime нет, проблема с возвратом уникального идентификатора. прийдется формировать его на стороне клиента и передавать в процедуру, а это не есть хорошо...

Можете выложить текст процедуры?

Мне кажется, принципиальной разницы не будет, формируется ли значение @ContractNewID внутри процедуры или формируется в конфигурации с помощью Connector.GenGUID() и передаётся в качестве параметра.

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

SET @ContractNewID = newid()
SELECT @ContractNewID AS Result

Результат возвращался корректно. Проверял на версии 3.0.2.231. Почему не работает у Вас - пока версий нет.

Кстати, заметил, что выложенная Вами процедура требует 4 входящих параметра. Вы учли это в Вашем запросе?

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

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

Приходится поднимать тему в связи с переходом на SQL2008.

1 AS [a] INTO #tmp
DROP TABLE #tmp
 
EXEC ProcedureName :ParamName
 
/*

при попытке выполнить метод Open() появляется ошибка о том, что комментарий должен быть завершен... вероятно побороть это нельзя?
Тогда каким образом можно вернуть значение из ХП в версии 3.0.2.244?

У Вас точно алиас таблицы в блоке FROM такой: */-- ?

извиняюсь, не доглядел... СПСБ...
только значение не возвращается всё равно, точнее возвращается NULL, хотя я точно знаю, что должна быть осмысленная строка... если запрос из профайлера выполнить в студии, то значение есть, но оно не попадает в датасет...
ХП:

ALTER PROCEDURE [dbo].[spAccountStatus] 
	@IDBalSys integer
AS
BEGIN
	declare @StatusAccount varchar(250)
	execute ('BEGIN SRVC.SERVICES_PKG.GETCLIENTSTATUSCRM(?,?); END;', @IDBalSys, @StatusAccount output) AT DEVELOPER;
	select @StatusAccount
END

скрипт:

var SelectQuery = Services.GetNewItemByUSI('sq_StatusAccount');
SelectQuery.Parameters.ItemsByName('IDBalSys').Value = IDBalSys;
var DatasetResult = SelectQuery.Open();
var StatusAccount = DatasetResult.Values('StatusAccount');

в прицепе файл сервиса...

Думаю, для решения проблемы необходимо изменить последнюю строку хранимой процедуры следующим образом:

  SELECT @StatusAccount as [StatusAccount]

Дело в том, что процедура в том виде, в котором она есть сейчас, возвращает результат в колонку с неопределённым именем. А Вам необходимо получить результат из колонки "StatusAccount".

СПСБ!

Здравствуйте!

Есть хранимая процедура, которая возвращает запрос (4 поля).

Подскажите, пожалуйста, каким образом их все вывести?

Вероятно должно быть так: одно поле должно быть создано, как описано в сообщениях 28 и 29, а остальные должны быть просто созданы, а вот в ХП, в результирующем запросе все 4 поля должны быть описаны, как в сообщении 31, т.е. каждому полю должен соответствовать псевдоним в точности повторяющий название поля в сервисе...

Здравствуйте, Марина.

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

Приблизительный код:

(хранимая процедура)

CREATE PROCEDURE tspTest
@isEnought1 [uniqueidentifier] output,
@isEnought2 [varchar](250) output,
@isEnought3 [varchar](250) output,
@isEnought4 [uniqueidentifier]output
AS
BEGIN 
 SET @isEnought1 = (select ID from tbl_Account Where ID = '{5F982A3D-0AAC-4306-8DBF-5F2327987615}')
 SET @isEnought2 = (select Name from tbl_Account Where ID = '{5F982A3D-0AAC-4306-8DBF-5F2327987615}')
 SET @isEnought3 = (select OfficialAccountName from tbl_Account Where ID = '{5F982A3D-0AAC-4306-8DBF-5F2327987615}')
 SET @isEnought4 = (select OwnerID from tbl_Account Where ID = '{5F982A3D-0AAC-4306-8DBF-5F2327987615}')
END
GO

(код скрипта)

function Main()
{
 
var Parameters = CreateSPParameters();
 
CreateSPParameter(Parameters, 'isEnought1', pdtGUID, 0);
CreateSPParameter(Parameters, 'isEnought2', pdtString, 0);
CreateSPParameter(Parameters, 'isEnought3', pdtString, 0);
CreateSPParameter(Parameters, 'isEnought4', pdtGUID, 0);
 
Parameters.ItemsByName('isEnought1').ParamType = 1; // It!
Parameters.ItemsByName('isEnought2').ParamType = 1; // It!
Parameters.ItemsByName('isEnought3').ParamType = 1; // It!
Parameters.ItemsByName('isEnought4').ParamType = 1; // It!
 
var SQLText = 'exec dbo.tspTest3 :isEnought1 OUTPUT, :isEnought2 OUTPUT, :isEnought3 OUTPUT, :isEnought4 OUTPUT';
Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);
 
var IsEnought1 = Parameters.ItemsByName('isEnought1').Value;
var IsEnought2 = Parameters.ItemsByName('isEnought2').Value;
var IsEnought3 = Parameters.ItemsByName('isEnought3').Value;
var IsEnought4 = Parameters.ItemsByName('isEnought4').Value;	
}

Спасибо! Получилось

у меня по аналогии не получается:

ALTER procedure [dbo].[mySQLProc]
@Result [varchar](250) output
AS
 begin         
         SET @Result = (SELECT TOP 1 Name FROM tbl_Account)
 end

var Parameters = CreateSPParameters();
CreateSPParameter(Parameters, 'Result', pdtString, 0);
Parameters.ItemsByName('Result').ParamType = 1; // It!
var SQLText = 'exec dbo.mySQLProc :Result OUTPUT';
 
Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);
var Result = Parameters.ItemsByName('Result').Value;
Log.Write(1, Result.ValAsStr);

Не получается, потому что хранимая процедура в MS SQL не возвращает строки.

Можно сделать хранимку такую:

ALTER procedure [dbo].[mySQLProc] --@Result varchar(250) output
AS
 begin         
         SELECT Name FROM tbl_Account WHERE Name Like '%Контакт%'
 end

и в MS SQL Managment Studio запросом:

DECLARE @Result varchar(250)
EXECUTE [dbo].[mySQLProc]
print @Result

будет выводиться нормальное значение, но при этом не ясно, как передать результат выборки в Terrasoft, так как ExecuteCustomSQL в Террасофте работает только с параметрами. Возможно у DBEngine есть еще какие лбо полезные методы, помимо ExecuteCustomSQL, но я их не обнаружил.

написал такую хранимку:

ALTER procedure [dbo].[mySQLProc]
@Result [varchar](250) output
AS
 begin         
         SELECT Name as Result FROM tbl_Account WHERE Name Like '%Контакт%'
 end

в MS SQL запрос

EXECUTE [dbo].[mySQLProc] @Result
print @Result

работает!!!

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

var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
         var ReturnParameter = Parameters.CreateItem();
         ReturnParameter.Name = 'Result';
         ReturnParameter.ParamType = 1;            
         ReturnParameter.DataType = 2;
         Parameters.Add(ReturnParameter);
         var sql = 'exec myProc :Result output';
         Connector.DBEngine.ExecuteCustomSQL(sql, Parameters);
         Label01.Caption = Parameters.ItemsByName('Result').ValAsStr;
         Log.Write(1, Parameters.ItemsByName('Result').ValAsStr);

и

var Parameters = CreateSPParameters();
		 CreateSPParameter(Parameters, 'Result', pdtString, 0);
		 Parameters.ItemsByName('Result').ParamType = 1; // It!
		 var SQLText = 'exec dbo.mySQLProc :Result OUTPUT';
		 Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);
		 var Result = Parameters.ItemsByName('Result').Value;
		 Log.Write(1, Result.ValAsStr); 

так тоже строку не возвращает:

		 var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
         var ReturnParameter = Parameters.CreateItem();
         ReturnParameter.Name = 'Result';
        ReturnParameter.ParamType = 1;           
        ReturnParameter.DataType = pdtString;
         Parameters.Add(ReturnParameter);
         Connector.DBEngine.ExecuteCustomSQL("SELECT :Result = Name FROM tbl_Account WHERE Name Like '%Контакт%'", Parameters);
         Log.Write(1, Parameters.ItemsByName('Result').Value+"");

Процедуру надо переделать:

alter procedure [dbo].[mySQLProc]
@Result nvarchar(250) output
AS
 begin         
         SELECT @Result = Name FROM tbl_Account WHERE Name Like '%Контакт%'
 end

Так будет работать, проверили!

"Виталий Ковалишин" написал:

Процедуру надо переделать:

ALTER procedure [dbo].[mySQLProc]

@Result nvarchar(250) output

AS

 begin        

         SELECT @Result = Name FROM tbl_Account WHERE Name LIKE '%Контакт%'

 end

Так будет работать, проверили!

--

www.it-sfera.com.ua

А скрипт в террасофте какой должен быть для этого кода?
Перепробовали массу скриптов с этим вариантом хранимой процедуры.

"Виталий Ковалишин" написал:

Процедуру надо переделать:

ALTER procedure [dbo].[mySQLProc]

@Result nvarchar(250) output

AS

 begin        

         SELECT @Result = Name FROM tbl_Account WHERE Name LIKE '%Контакт%'

 end

Так будет работать, проверили!

--

www.it-sfera.com.ua

Все получилось! Спасибо Виталию! С этим запросом заработал срипт:
var Parameters = Sy

stem.CreateObject('TSObjectLibrary.Parameters');
var ReturnParameter = Parameters.CreateItem();
         var ReturnParameter = Parameters.CreateItem();
         ReturnParameter.Name = 'Result';
         ReturnParameter.ParamType = 1;            
         ReturnParameter.DataType = 3;
         Parameters.Add(ReturnParameter);
Parameters.ItemsByName('Result').ParamType = 1; // It!
var SQLText = 'exec dbo.mySQLProc :Result OUTPUT';
Connector.DBEngine.ExecuteCustomSQL(SQLText, Parameters);
var rez = Parameters.ItemsByName('Result').ValAsStr;
Log.Write(1, rez+"");