Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Никак не могу заставить работать следующую процедуру - (работающие части убраны), а именно правильно вызвать

sp_executesql

ПРичем не происходит срабатывания CATCH, процедура заканчивается COMMIT TRAN; приведенная секция не работает - т.е. не происходить вставки строк товарных позиций в документ

Путем всяких шаманств установил, что программа спотыкается на первом же вызове sp_executesql

Что я делаю неправильно?

=====================================================

PROC [dbo].[SP_НовыйПроектИзРабочейСпецификации] @номерспецификации int, @предприятие varchar(100),  
   @предприятие1 varchar(100) = 'ххххххх',   @sotr int,   @t  TINYINT = 4,  @errmes varchar(5000) = '' OUTPUT, 
    @nnew Varchar(10) OUTPUT
AS 

 DECLARE @INFO NVARCHAR(4000) 
 DECLARE @TEXT NVARCHAR(4000)
 DECLARE @TName NVARCHAR(128)
 DECLARE @PARAMDefinition NVARCHAR(100)
 DECLARE @CNTSTR INT
 DECLARE @Name VARCHAR(250)
 DECLARE @NNewProj int;
 DECLARE @NNew_ZakaziDokumenti int
 DECLARE @mesto NVARCHAR(20)
 
 SET @TName = 'TempSpecOut' + CAST(@sotr AS VARCHAR(14)) 
 
 
IF EXISTS(SELECT * FROM [ЖурналПодСпецификаций] WHERE [№№]= @НомерСпецификации)
BEGIN


BEGIN TRY
 BEGIN TRAN;

------------- тут часть текста удалена - все работает нормально

SET @TEXT = N'SELECT @ctn = COUNT(*) FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали]=ZT.[товар по базе]
             WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + '])  
             and ZT.Nтпд is null'
             
SET @ParamDefinition = N'@cnt INT OUTPUT'

--не работает отсуда выполнение сразу переходит в конец на COMMIT TRAN
EXECUTE @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT 

If (SELECT @CNTSTR) > 0 
 BEGIN
  SET @TEXT = N'INSERT INTO [Заказы Товар]  ([товар по базе], [товар по документу], [Сотрудник], [Добавлено])
	SELECT JPSN.[КодДетали] as [товар по базе], (SELECT Товар FROM [База товаров] WHERE [№№ товара] = JPSN.[КодДетали] ), '+ CAST(@Sotr AS VARCHAR(14)) + ', getdate() 
	FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали] = ZT.[товар по базе]
	WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали  IN (SELECT КодИзделия FROM [' + @TName + ']) and ZT.Nтпд is null'    
  
   EXECUTE sp_executesql @TEXT
 END

 SET @TEXT = N'INSERT INTO [Заказы товары в документах]   ([Nдп], [Nтпд], [Nеи], [ндс], [Количество], [Мест],  [Сотрудник], [Добавлено], [Цена], НомСпецификации, МатериалЗамена )
	   SELECT DISTINCT ' + CAST(@NNew_ZakaziDokumenti AS VARCHAR(100)) + ' as [Nдп], (SELECT TOP 1 Nтпд FROM [Заказы Товар] ZT WHERE JPSN.[КодДетали] = ZT.[товар по базе]), [Нестандарт], 18, [КоличествоШт], 0, ' 
	   + CAST(@Sotr AS VARCHAR(14)) + ', getdate(), ROUND([ЦенаСогл]/1.18,2), ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ', Себестоимость2 
     	FROM [ЖурналПодСпецНабор] JPSN 
	    WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали  IN (SELECT КодИзделия FROM [' + @TName + '])'  
 
  EXECUTE sp_executesql @TEXT 



END TRY

BEGIN CATCH
  SET @INFO =  ISNULL(@INFO,'') + ' ' + @mesto  +  ':  Вставка данных в проект ' +
        CAST(ERROR_NUMBER() AS VARCHAR(20)) + ' - ErrorNumber, Описание:' +
        ERROR_PROCEDURE() + ', ' + CAST(ERROR_LINE() AS VARCHAR(1000)) + ', ' +
        ERROR_MESSAGE() 
END CATCH





IF @@ERROR>0 
 BEGIN 
  SET @ErrMes = @INFO 
  ROLLBACK TRAN 
  RETURN 1
 END
ELSE 
 BEGIN
  Set @NNew = @NNewProj
  COMMIT TRAN;
 END
END;


Сообщение было отредактировано: 3 фев 12, 12:43
3 фев 12, 12:40    [12025476]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ошибка-то какая?
3 фев 12, 12:41    [12025485]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
дык нету никакой ошибки - просто не происходит вставки строк в таблицу
3 фев 12, 12:43    [12025490]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/c8b43477-b6c0-49bf-a608-394a0b6cc7a2.htm
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.
3 фев 12, 12:44    [12025514]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Спсибо, понял, попробую
3 фев 12, 12:45    [12025527]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
garvy
Путем всяких шаманств установил, что программа спотыкается на первом же вызове sp_executesql

А просто в Профайлере потрейсить, что происходит на сервере ?
3 фев 12, 12:47    [12025549]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
garvy
Спсибо, понял, попробую
Я как бы намекнул, что последний IF у вас всегда будет в секцию ELSE заворачивать, независимо, сработал CATCH или нет.
3 фев 12, 12:49    [12025565]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
EXECUTE @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT 

Это вообще, что за синтаксис вы придумали ?
Вы его вручную в QA запускали хоть ?
3 фев 12, 12:52    [12025613]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
1) начет протестить конечно бы неплохо - я тут уже поднимал тему - с переходом на SQL SERVER 2008 с 2000 я напрочь лишился возможности заниматься дебаггингом - так я и не осилил как это сделать в 2008. Поэтому приходится буквально извращаться, чтобы понять в каком месте процедура сбоит.
2) начет синтаксиса- брал вроде как из BOL. Если чего - неправильно - значит я чего то не понял - покажите чего?
3 фев 12, 12:57    [12025662]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Победа, ребята! Отключил секцию TRY и все заработало! Правда не понял чем она мешала..
3 фев 12, 13:01    [12025714]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
garvy
2) начет синтаксиса- брал вроде как из BOL. Если чего - неправильно - значит я чего то не понял - покажите чего?

Команда EXECUTE все жизнь запускала на выполние процедуры
3 фев 12, 13:02    [12025722]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
К тому же в @TEXT нет никакого @cnt !!
3 фев 12, 13:04    [12025752]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
EXECUTE @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT - это механическая ошибка когда я сообщение подготавливал,
в процедуре все естесттвенно правильно:
EXECUTE SP_ExecSQL @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT
3 фев 12, 13:05    [12025768]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
garvy
Победа, ребята! Отключил секцию TRY и все заработало! Правда не понял чем она мешала..
Хотите сказать, что у вас работает
SET @TEXT = N'SELECT @ctn = COUNT(*) FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали]=ZT.[товар по базе]
             WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + '])  
             and ZT.Nтпд is null'
             
SET @ParamDefinition = N'@cnt INT OUTPUT'

--не работает отсуда выполнение сразу переходит в конец на COMMIT TRAN
EXECUTE @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT 
?
Тогда у вас не MSSQL...
3 фев 12, 13:05    [12025780]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Хочу сказать, что да, таки работает. А что тут неправильного - смотрел в пример вызова процедуры и так и сяк - не вижу в чем я неправ?

на основе этого примера делал:
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
3 фев 12, 13:40    [12026170]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
а не, я слишком самоуверен - не работает
3 фев 12, 13:44    [12026200]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
declare @CNTSTR int
If (SELECT @CNTSTR) > 0 SELECT 'Am i right'
ELSE
	SELECT 'Am i wrong'
3 фев 12, 13:44    [12026202]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
насчет механического пропуска SP_ExecSQL я уже написал
3 фев 12, 13:45    [12026220]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
в общем ересь какая-то, теперь работает. До этого не работало. Крыша уже едет, с самого утра с этой проклятой процедурой вожусь. Привожу полный исправленный текст:
USE [VS_ShEn]
GO
/****** Object: StoredProcedure [dbo].[SP_НовыйПроектИзРабочейСпецификации] Script Date: 02/03/2012 13:56:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- создает новый проект на основе спецификации
-- номер спецификации копируется в поле
-- в проект вставляются только выбранные из спецификации позиции

ALTER PROC [dbo].[SP_НовыйПроектИзРабочейСпецификации] @номерспецификации int, @предприятие varchar(100),
@предприятие1 varchar(100) = 'ххххххх', @sotr int, @t TINYINT = 4, @errmes varchar(5000) = '' OUTPUT,
@nnew Varchar(10) OUTPUT
-- xxx.dts.CR_Proc
AS
/* Копирует:
Заказы Проекты
Заказы 1цепочка - 2-е записи (1-ый + 2-ой этапы)
Заказы Документы
Заказы товары в документах ([Заказы Товар] - при нехватке)
*/
DECLARE @INFO NVARCHAR(4000)
DECLARE @TEXT NVARCHAR(4000)
DECLARE @TName NVARCHAR(128)
DECLARE @PARAMDefinition NVARCHAR(100)
DECLARE @CNTSTR INT
DECLARE @Name VARCHAR(250)
DECLARE @NNewProj int;
DECLARE @NNew_ZakaziDokumenti int


SET @TName = 'TempSpecOut' + CAST(@sotr AS VARCHAR(14))


IF EXISTS(SELECT * FROM [ЖурналПодСпецификаций] WHERE [№№]= @НомерСпецификации)
BEGIN


EXEC PR_GEN_ID_INC 'Заказы Проекты', 'Nпроекта', @NNewProj output;

BEGIN TRAN;

--название предприятия
Set @name = (SELECT Предприятие FROM [Клиенты Адреса заказчиков] WHERE Заказчик = @Предприятие1)

---- формируем новый проект
INSERT INTO [Заказы Проекты] ([Nпроекта], [Название проекта], [Месяц], [Год], [Тип сделки], [Nвид оплаты] , [Метка состояния], [Заказчик], [Добавлено], [Дата начала])
SELECT @NNewProj, LTRIM(@name+' - '+dbo.NameMonth_Rus(MONTH(getdate()))+'-'+LTRIM(STR(YEAR(getdate())))), MONTH(getdate()), YEAR(getdate()), 1, 1, 0, @Sotr, getdate(), getdate()
FROM [ЖурналПодСпецификаций] JPS LEFT JOIN [Клиенты База заказчиков] KBK ON JPS.[Заказчик] = KBK.[Заказчик]
WHERE [№№]=@НомерСпецификации;
-- вставляем этапы проекта
-- 1 этап - склад - Предприятие */
INSERT INTO [Заказы 1цепочка] ([Nпроекта], [Nподпроекта], [Продавец], [Покупатель], [Последовательность], [Договор], [Тип сделки], [Nвид оплаты], [Примечание], [Сотрудник], [Добавлено])
VALUES (@NNewProj, LTRIM(STR(@NNewProj)+'-1'), 'склад', @Предприятие,1, null, '0000', 1, null, @Sotr, getdate());

-- 2 этап - Предприятие - Заказчик
INSERT INTO [Заказы 1цепочка] ([Nпроекта], [Nподпроекта], [Продавец], [Покупатель], [Последовательность], [Договор], [Тип сделки], [Nвид оплаты], [Примечание], [Сотрудник], [Добавлено])
VALUES (@NNewProj, LTRIM(STR(@NNewProj)+'-2'),@Предприятие, @Предприятие1, 2, null, '0000', 1, null, @Sotr, getdate());
-- вставляем первый документ проекта
INSERT INTO [Заказы Документы] ( [Nдокумента], [Тип], [Поток], [Дата], [Nвид оплаты], [Nподпроекта], [NNподпроекта], [Копия], [Условная проводка], [Учет], [Факт], [резерв], [Закрыт], [Примечание], [Сумма], [Актуальность], [Печать], [Сотрудник], [Добавлено])
VALUES ( null, 14, '0000', null, 0, LTRIM(STR(@NNewProj)+'-2'), null, 0, 0, 0, 0, 0, 0, null, 0, 'проект', 0, @Sotr, getdate());


SET @NNew_ZakaziDokumenti=@@IDENTITY;
-- вставляем содержание документа

---секция нужна только для вставки новых товаров в каталог
SET @TEXT = N'SELECT @ctn = COUNT(*) FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали]=ZT.[товар по базе]
WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + '])
and ZT.Nтпд is null'
SET @ParamDefinition = N'@cnt INT OUTPUT'

EXECUTE sp_executesql @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT
If (SELECT @CNTSTR) > 0
BEGIN
SET @TEXT = N'INSERT INTO [Заказы Товар] ([товар по базе], [товар по документу], [Сотрудник], [Добавлено])
SELECT JPSN.[КодДетали] as [товар по базе], (SELECT Товар FROM [База товаров] WHERE [№№ товара] = JPSN.[КодДетали] ), '+ CAST(@Sotr AS VARCHAR(14)) + ', getdate()
FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали] = ZT.[товар по базе]
WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + ']) and ZT.Nтпд is null'

EXECUTE sp_executesql @TEXT
END
------- конец секции


-- непостердственно вставка товарных позиций - работает в любом случае
SET @TEXT = N'INSERT INTO [Заказы товары в документах] ([Nдп], [Nтпд], [Nеи], [ндс], [Количество], [Мест], [Сотрудник], [Добавлено], [Цена], НомСпецификации, МатериалЗамена )
SELECT DISTINCT ' + CAST(@NNew_ZakaziDokumenti AS VARCHAR(100)) + ' as [Nдп], (SELECT TOP 1 Nтпд FROM [Заказы Товар] ZT WHERE JPSN.[КодДетали] = ZT.[товар по базе]), [Нестандарт], 18, [КоличествоШт], 0, '
+ CAST(@Sotr AS VARCHAR(14)) + ', getdate(), ROUND([ЦенаСогл]/1.18,2), ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ', Себестоимость2
FROM [ЖурналПодСпецНабор] JPSN
WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + '])'

EXECUTE sp_executesql @TEXT



--- обработка выхода
IF @@ERROR>0
BEGIN
SET @ErrMes = @@ERROR
ROLLBACK TRAN
RETURN 1
END
ELSE
BEGIN
Set @NNew = @NNewProj
COMMIT TRAN;
END
END;
3 фев 12, 14:03    [12026394]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
Glory
Member

Откуда:
Сообщений: 104751
garvy
в общем ересь какая-то, теперь работает. До этого не работало. Крыша уже едет, с самого утра с этой проклятой процедурой вожусь. Привожу полный исправленный текст:

Вы ответы читаете ?
3 фев 12, 14:05    [12026419]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
читаю, но не понимаю где я неправ.
3 фев 12, 14:18    [12026560]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
garvy
в общем ересь какая-то
Конечно ересь.
Про использование @@error вам уже говорили -- 12025514. От @@identity так же нужно избавиться. Не говоря уж об архитектурной ошибке, приводящей к нужде использовать динамику.
3 фев 12, 14:19    [12026569]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Уже у меня крышу сносит. Хорошо, по поводу @@Error. Правильно я поступаю:

DECLARE @e1 INT

---секция нужна только для вставки новых товаров в каталог
SET @TEXT = N'SELECT @ctn = COUNT(*) FROM [ЖурналПодСпецНабор] JPSN LEFT JOIN [Заказы Товар] ZT ON JPSN.[КодДетали]=ZT.[товар по базе]
WHERE JPSN.[КодСпецРаб] = ' + CAST(@НомерСпецификации AS VARCHAR(100)) + ' AND JPSN.КодДетали IN (SELECT КодИзделия FROM [' + @TName + '])
and ZT.Nтпд is null'
SET @ParamDefinition = N'@cnt INT OUTPUT'

EXECUTE sp_executesql @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT

IF @@ERROR>0
SET @E1 = @@ERROR

К счастью у меня запустилась отладка. Дохожу до этого места, срабатывает EXECUTE sp_executesql @TEXT, @ParamDefinition, @cnt = @CNTSTR OUTPUT

После этого срабатывает и
IF @@ERROR>0
SET @E1 = @@ERROR

Но вот беда @e1 = 0 в отладочном окошке локальных переменных после срабатывания
SET @E1 = @@ERROR

Так где ошибка?
3 фев 12, 15:32    [12027641]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
garvy
Member

Откуда: Москва
Сообщений: 801
Процедура эта вызывается из приложения в ACCESS. Раньше все пользователи пользовались общей временной таблицей для выбора номенклатуры, которую надо вставить в документ. Стали друг другу мешать. Поэтому я динамически завожу под каждого пользователя свою таблицу с названием 'TempSpecOut' + @Sotr, где @Sotr - код пользователя.
3 фев 12, 15:35    [12027691]     Ответить | Цитировать Сообщить модератору
 Re: не могу заттавит работать exec sp_executesql  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
garvy,

в тексте запроса @ctn, а в описании параметров - @cnt
Или это ошибка копипаста при выкладывании на сервере?
Раз мой первый сигнал успешно проигнорирован...
3 фев 12, 15:38    [12027733]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить