Создание динамических запросов, sp_executesql

@iluxa1810 · Регистрация: 03.09.2011

Студворк — интернет-сервис помощи студентам

В общем проблема вот в чем:
Запрос прекрасно работает, но я его захотел сделать динамическим, что бы положить его в одной БД и не пихать по разным.
Проблема в том, что sp_executesql не возвращает новые значения в процедуру и там так и остаются 0...
Хотя я тестирую на случае, когда процедура должна возвращать @ExitState = 3.
Без динамической структуры все работало, а сейчас нет=(

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
ALTER PROCEDURE [dbo].[ExportChV3]
@ExitCode INT = 0 OUT,
@ExitState INT = 0 OUT,
@project_id INT,
@task_id INT ,
@step_id INT
AS
BEGIN
SET NOCOUNT ON; 
 
 
DECLARE @a TABLE (
[schema_name] NVARCHAR(50) NOT NULL,    --Временная таблица, куда копируется 1 предыдщуее значение
[state] TINYINT NOT NULL,
[description] NVARCHAR(255) NULL,
[creater_login] NVARCHAR(50) NULL,
[create_date] DATETIME NULL,
[cfg] XML NULL
);
 
DECLARE @b NVARCHAR(50)=NULL; -- Временные переменные для смены названий
DECLARE @SQL NVARCHAR(MAX);
DECLARE @db NVARCHAR(50);
DECLARE @param NVARCHAR(200)
SELECT db=dbname FROM Octopus2.dbo.Project WITH(nolock)
WHERE Project_id = @Project_Id --Declare @c NVARCHAR(50);
SET @SQL=N'IF NOT EXISTS(
SELECT *
FROM [%DBName%].dbo.ExportInfo
)
BEGIN
SET @ExitState = 3,
@ExitCode = 0 --Если выгрузок нет, значит выгрузка не была настроена,
--так как первая выгрузка создается сразу после сохранения настроек
END
ELSE 
IF EXISTS(
SELECT TOP 1 [state]
FROM [%DBName%].dbo.ExportInfo ei -- Проверяем есть ли что-нибудь открытое
WHERE ei.[state] = 1
)
BEGIN
SET @ExitCode = 0,
@ExitState = 0 -- Тогда ничего не делаем
END
ELSE
--IF
--(SELECT TOP 1 [state] FROM [%DBName%].dbo.ExportInfo ei -- Иначе если все закрыто
-- ORDER BY ei.[state] DESC)=0
BEGIN
INSERT @a
(
[schema_name],
[state],
[description],
[creater_login],
[create_date],
[cfg]
)
SELECT TOP 1 [schema_name],
[state],
[description],
[creater_login],
[create_date],
CAST([cfg] AS XML) -- Делаем копию первой выгрузки
FROM [%DBName%].dbo.ExportInfo ei
ORDER BY
ei.ei_id DESC
 
 
SELECT @b = MASTER.Regex.Match([schema_name], ''(?<=\w_)\d+'') + 1
FROM @a
 
UPDATE @a
SET [SCHEMA_NAME] = REPLACE(
[SCHEMA_NAME],
MASTER.RegEx.Match([schema_name], ''\d+''),
@b
),  -- присваиваем новый номер формы
[cfg].modify(
''replace value of (/configuration/Octopus.Xts/ExportSchemaNumber/text())[1]
with sql:variable("@b")''
),  -- В конфигурации находим название выгрузки и ставим новый
[create_date] = GETDATE(),
[STATE] = 1 -- Получаем дату создания, и делаем ее открытой
END
'
SET @SQL=REPLACE(@SQL,'[%DBName%]',@db)
SET @param=N'@ExitCode INT OUTPUT,@ExitState INT OUTPUT,@b NVARCHAR(50) OUTPUT'
EXEC sp_executesql @SQL,@param,@ExitCode OUTPUT,@ExitState OUTPUT,@b OUTPUT
 
SELECT @ExitCode,@ExitState
 
IF @b IS NOT NULL
BEGIN
BEGIN TRAN 
 
INSERT dbo.ExportInfo -- Создаем выгрузку
(
[schema_name],
[state],
[description],
creater_login,
create_date,
cfg
)
SELECT [schema_name],
[state],
[description],
creater_login,
create_date,
CAST(cfg AS NVARCHAR(MAX))
FROM @a
 
UPDATE t --Меняем выгрузку в конфигурации формы
SET t.Xmlconfig = CAST(@b AS NTEXT)
FROM octopus2.dbo.Task t WITH(NOLOCK)
WHERE t.Project_id = @project_id
AND t.Task_id = @task_id
AND t.Step_id = @step_id -- параметры
 
SELECT @ExitCode = 0,
@ExitState = 0 
 
COMMIT TRAN
END
 
 
END

Добавлено через 14 часов 36 минут
Помогите, надо всего лишь сказать мне, как правильно из sp_executesql взять данные для основной процедуры, и вернуть их.

@Kruds · 15.02.2014, 17:13

Думаю что нужно исправить на это:

T-SQL
1
2
EXEC sp_executesql @SQL,@param,@ExitCode=@ExitCode OUTPUT,
@ExitState=@ExitState OUTPUT, @b=@b OUTPUT

@iluxa1810 · 15.02.2014, 17:26 **[ТС]**

Если не мне не изменяет память, то когда я пытался так сделать на работе была ругань, что переменные не обхявлены...

@Kruds · 15.02.2014, 17:45

Попробуйте. Вот накидал на коленке похожую ситуацию:

T-SQL
1
2
3
4
5
declare @var as int
declare @sqlText as nvarchar(100) = 'select @var = 100500'
declare @parameters as nvarchar(100) = '@var int OUTPUT'
exec sp_executesql @sqlText, @parameters, @var = @var OUTPUT
select @var

@iluxa1810 · 15.02.2014, 18:29 **[ТС]**

Вроде работает, спасибо !
На работе попробую данный способ.

@iluxa1810 · 17.02.2014, 09:38 **[ТС]**

SQL
1
2
Msg 119, Level 15, State 1, PROCEDURE ExportChV3, Line 93
Must pass parameter NUMBER 5 AND subsequent parameters AS '@name = value'. After the form '@name = value' has been used, ALL subsequent parameters must be passed IN the form '@name = value'.

Мне вот такую ошибку выдает в sp_executesql . одсвечивает строку c Exec...

Добавлено через 17 минут
Косякнул немного.
Вот так компилируется, однако все равно мне 0 выводятся...
Я даже пробовал непосредственно само тело процедуры запускать, но все равно 0 выбираются.
В этом коде я немного обрезал процедурку, так как UPDATE не нужен для првоерки работы.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
ALTER PROCEDURE [dbo].[ExportChV3]
    @ExitCode INT = 0 OUT,
    @ExitState INT = 0 OUT,
    @project_id INT,
    @task_id INT ,
    @step_id INT
AS
BEGIN
    SET NOCOUNT ON; 
    
    
    DECLARE @a TABLE (
                [schema_name] NVARCHAR(50) NOT NULL,    --Временная таблица, куда копируется 1 предыдщуее значение
                [state] TINYINT NOT NULL,
                [description] NVARCHAR(255) NULL,
                [creater_login] NVARCHAR(50) NULL,
                [create_date] DATETIME NULL,
                [cfg] XML NULL
            );
    
    DECLARE @b NVARCHAR(50)=NULL; -- Временные переменные для смены названий
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @db NVARCHAR(50);
    DECLARE @param NVARCHAR(200)
    SELECT db=dbname FROM Octopus2.dbo.Project WITH(nolock)
                         WHERE Project_id = @Project_Id                         --Declare @c NVARCHAR(50);
SET @SQL=N'IF NOT EXISTS(
           SELECT *
           FROM   [%DBName%].dbo.ExportInfo
       )
    BEGIN
        SET @ExitState = 3,
               @ExitCode = 0 --Если выгрузок нет, значит выгрузка не была настроена,
                             --так как первая выгрузка создается сразу после сохранения настроек
    END
    ELSE 
    IF EXISTS(
           SELECT TOP 1 [state]
           FROM   [%DBName%].dbo.ExportInfo ei -- Проверяем есть ли что-нибудь открытое
           WHERE  ei.[state] = 1
       )
    BEGIN
        SET @ExitCode = 0,
               @ExitState = 0 -- Тогда ничего не делаем
    END
    ELSE
        --IF
        --(SELECT TOP 1 [state] FROM [%DBName%].dbo.ExportInfo ei   -- Иначе если все закрыто
        -- ORDER BY ei.[state] DESC)=0
    BEGIN
        INSERT @a
          (
            [schema_name],
            [state],
            [description],
            [creater_login],
            [create_date],
            [cfg]
          )
        SELECT TOP 1 [schema_name],
               [state],
               [description],
               [creater_login],
               [create_date],
               CAST([cfg] AS XML) -- Делаем копию первой выгрузки
        FROM   [%DBName%].dbo.ExportInfo ei
        ORDER BY
               ei.ei_id DESC
        
        
        SELECT @b = MASTER.Regex.Match([schema_name], ''(?<=\w_)\d+'') + 1
        FROM   @a
        
        UPDATE @a
        SET    [SCHEMA_NAME] = REPLACE(
                   [SCHEMA_NAME],
                   MASTER.RegEx.Match([schema_name], ''\d+''),
                   @b
               ),   -- присваиваем новый номер формы
               [cfg].modify(
                   ''replace value of (/configuration/Octopus.Xts/ExportSchemaNumber/text())[1]
                with sql:variable("@b")''
               ),   -- В конфигурации находим название выгрузки и ставим новый
               [create_date] = GETDATE(),
               [STATE] = 1 -- Получаем дату создания, и делаем ее открытой
               END
        '
       SET @SQL=REPLACE(@SQL,'[%DBName%]',@db)
    SET @param=N'@ExitCode INT OUTPUT,@ExitState INT OUTPUT,@b NVARCHAR(50) OUTPUT'
    EXEC sp_executesql @SQL,@param,@ExitCode=@ExitState OUTPUT,@ExitState=@ExitState OUTPUT,@b=@b OUTPUT
    
    SELECT @ExitCode,@ExitState
END

Добавлено через 20 минут
Я даже в строку перед всеми условиям добавлял select 10, но ничего не происходило, такое впечатление , что EXEC не срабатывает...

@Kruds · 17.02.2014, 09:40

Во первых явно неправильное присваивание в вызове sp_executesql

Сообщение от iluxa1810

@ExitCode=@ExitState

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

@iluxa1810 · 17.02.2014, 23:19 **[ТС]**

Сообщение от Kruds

Во первых явно неправильное присваивание в вызове sp_executesql

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

Select я для проверки использовалю

Т.е ты хочешь сказать, что надо OUTPUT убрать в вызове sp_... ?

@Kruds · 18.02.2014, 08:04

Нет, output как раз не лишний. Я бы все же проверил логику запроса в переменной @sql, в остальном я явных ошибок не вижу.

Новые блоги и статьи Все статьи Все блоги /
Установка Android SDK, NDK, JDK, CMake и т.д. 8Observer8 25.01.2026 Содержание блога Перейдите по ссылке: https:/ / developer. android. com/ studio и в самом низу страницы кликните по архиву "commandlinetools-win-xxxxxx_latest. zip" Извлеките архив и вы увидите. . .	Вывод текста со шрифтом TTF на Android с помощью библиотеки SDL3_ttf 8Observer8 25.01.2026 Содержание блога Если у вас не установлены Android SDK, NDK, JDK, и т. д. то сделайте это по следующей инструкции: Установка Android SDK, NDK, JDK, CMake и т. д. Сборка примера Скачайте. . .	Использование SDL3-callbacks вместо функции main() на Android, Desktop и WebAssembly 8Observer8 24.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	моя боль iceja 24.01.2026 Выложила интерполяцию кубическими сплайнами www. iceja. net REST сервисы временно не работают, только через Web. Написала за 56 рабочих часов этот сайт с нуля. При помощи perplexity. ai PRO , при. . .
Модель сукцессии микоризы anaschu 24.01.2026 Решили писать научную статью с неким РОманом	http://iceja.net/ математические сервисы iceja 20.01.2026 Обновила свой сайт http:/ / iceja. net/ , приделала Fast Fourier Transform экстраполяцию сигналов. Однако предсказывает далеко не каждый сигнал (см ограничения http:/ / iceja. net/ fourier/ docs ). Также. . .	http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь(не выше 3-го порядка) постоянного тока с элементами R, L, C, k(ключ), U, E, J. Программа находит переходные токи и напряжения на элементах схемы классическим методом(1 и 2 з-ны. . .

@iluxa1810 13 / 13 / 11 Регистрация: 03.09.2011 Сообщений: 1,026
	15.02.2014, 17:26 [ТС]
	Если не мне не изменяет память, то когда я пытался так сделать на работе была ругань, что переменные не обхявлены... 0

@iluxa1810 13 / 13 / 11 Регистрация: 03.09.2011 Сообщений: 1,026
	15.02.2014, 18:29 [ТС]
	Вроде работает, спасибо ! На работе попробую данный способ. 0

@Kruds 708 / 708 / 226 Регистрация: 04.03.2013 Сообщений: 1,384
	18.02.2014, 08:04
	Нет, output как раз не лишний. Я бы все же проверил логику запроса в переменной @sql, в остальном я явных ошибок не вижу. 0

Создание динамических запросов, sp_executesql

Решение