Хранимая процедура не возвращает результаты из табличной переменной?

У меня есть хранимая процедура, над которой я работаю, и я пытаюсь заставить ее возвращать результаты, которые хранятся в табличной переменной, которую я создал в процедуре. По какой-то причине он ничего не возвращает, когда я выполняю процедуру. Однако, если я возьму фрагмент кода, используемый для заполнения табличной переменной, и уберу его из процедуры, он сработает...

Код выглядит следующим образом:

Тестовый код

DECLARE @RC int
DECLARE @constraint_names nvarchar(max)
DECLARE @column_names nvarchar(max)
DECLARE @table_name nvarchar(max)
DECLARE @table_schema nvarchar(max)
DECLARE @database_name nvarchar(max)
DECLARE @debug int
DECLARE @ExistFlag int;

-- TODO: Set parameter values here.
SET @column_names = 'M2016_Object_ID int, Name varchar(50), a int, b'
SET @table_name = 'tblM2016_Objects';
SET @debug = 0;
SET @ExistFlag = 1;

DECLARE @existing_cols TABLE (Value nvarchar(max));
INSERT INTO @existing_cols EXEC spM2016_CheckColumnExistence_tblM2016 
@column_names, @table_name,  @database_name, @table_schema, 1, 0;
select * from @existing_cols

Результаты

Value
M2016_Object_ID int
Name varchar(50)

Это ожидаемый результат, поскольку я проверяю, существуют ли столбцы M2016_Object_ID и Name в таблице tblM2016_Objects.

Однако, когда я выполняю это внутри хранимой процедуры, над которой я работаю, она ничего не возвращает.

Хранимая процедура

ALTER PROCEDURE spM2016_ChangePKConstraints_tblM2016 
-- Add the parameters for the stored procedure here
    @constraint_names   nvarchar(max) = 
N'PK_tblM2016_Fields_M2016_Field_ID',
    @column_names       nvarchar(max) = N'M2016_Field_ID',
    @table_name         nvarchar(max) = N'tblM2016_Fields', 
    @table_schema       nvarchar(max) = N'dbo',
    @database_name      nvarchar(max) = N'MET',
    @debug              int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @tbl_name       nvarchar(max) = N'tblM2016_Fields',
        @col_names      nvarchar(max) = N'M2016_Field_ID',
        @tbl_schema     nvarchar(max) = N'dbo',
        @db_name        nvarchar(max) = N'MET',
        @tbl_full_name  nvarchar(max),
        @tbl_short_name nvarchar(max),
        @sql            nvarchar(max),
        @params         nvarchar(max)

-- Insert statements for procedure here

SET @col_names = ISNULL(NULLIF(@column_names, ''), @col_names);
SET @tbl_name = quotename(ISNULL(NULLIF(@table_name, ''), @tbl_name));
SET @tbl_schema = quotename(ISNULL(NULLIF(@table_schema, ''), @tbl_schema));
SET @db_name = quotename(ISNULL(NULLIF(@database_name, ''), @db_name));

SET @tbl_full_name =  @db_name + '.' + @tbl_schema + '.' + @tbl_name;
SET @tbl_short_name = @tbl_schema + '.' + @tbl_name;

DECLARE @existing_cols TABLE (Value nvarchar(max));
DECLARE @nonexisting_cols TABLE (Value nvarchar(max));

--INSERT INTO @split_columns
--  SELECT *
--  FROM 
--  fnM2016_Split_String_Inline(@col_names, ',');

--IF (@debug = 1)
--  SELECT * FROM @split_columns;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'PK' AND parent_object_id = OBJECT_ID(@tbl_full_name))
BEGIN
    -- No PK constraint, check col_names to see which exist and don't exist
    INSERT INTO @existing_cols EXEC spM2016_CheckColumnExistence_tblM2016 @col_names, @tbl_name,  @db_name, @tbl_schema, 1, 0;

    INSERT INTO @nonexisting_cols EXEC spM2016_CheckColumnExistence_tblM2016 @col_names, @tbl_name,  @db_name, @tbl_schema, 0, 0;

    SELECT * FROM @existing_cols;

    SELECT * FROM @nonexisting_cols;

END
--ELSE
--BEGIN

--END

END

Результаты Это результаты после выполнения процедуры с теми же значениями параметров в тесте выше:

EXECUTE @RC = [dbo].[spM2016_ChangePKConstraints_tblM2016] 
 @constraint_names
,@column_names
,@table_name
,@table_schema
,@database_name
,@debug;

Результат

Value

Таблица намеренно не имеет первичного ключа. Доказательство:

Jake 28.12.2016 источник

comment

В вашей процедуре у вас есть имя таблицы как @table_name nvarchar(max) = N'tblM2016_Fields', но оно должно быть tblM2016_Objects - SS_DBA 28.12.2016

comment

Можете ли вы показать фактические значения, которые вы передаете процедуре spM2016_ChangePKConstraints_tblM2016? - SS_DBA 28.12.2016

comment

Параметры, используемые в тестовом блоке кода, — это те же параметры, которые передаются в spM2016_ChangePKConstraints_tblM2016. - Jake 28.12.2016

comment

Какой порядок параметров в spM2016_CheckColumnExistence_tblM2016 - SS_DBA 28.12.2016

Ответы (2)

arrow_upward
1
arrow_downward

В своей процедуре измените имя таблицы в разделе Declare.

Это должно решить проблему.

ALTER PROCEDURE spM2016_ChangePKConstraints_tblM2016 
-- Add the parameters for the stored procedure here
    @constraint_names   nvarchar(max) = 
N'PK_tblM2016_Fields_M2016_Field_ID',
    @column_names       nvarchar(max) = N'M2016_Field_ID',
    @table_name         nvarchar(max) = N'tblM2016_Objects', 
    @table_schema       nvarchar(max) = N'dbo',
    @database_name      nvarchar(max) = N'MET',
    @debug              int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @tbl_name       nvarchar(max) = N'tblM2016_Objects',
        @col_names      nvarchar(max) = N'M2016_Field_ID',
        @tbl_schema     nvarchar(max) = N'dbo',
        @db_name        nvarchar(max) = N'MET',
        @tbl_full_name  nvarchar(max),
        @tbl_short_name nvarchar(max),
        @sql            nvarchar(max),
        @params         nvarchar(max)

SS_DBA 28.12.2016

comment

Я не думаю, что это сработает, потому что мой spM2016_CheckColumnExistence имеет те же параметры по умолчанию, и это не приводит к сбою, когда я прямо вызываю его в тестовом коде. Эти значения в основном используются для сравнений с нулевым значением в случае, если параметр имени таблицы передается в виде нулевой или пустой строки. - Jake; 28.12.2016

arrow_upward
0
arrow_downward

Я понял проблему. Мои @table_name, @table_schema и @database_name, переданные в процедуру spM2016_CheckColumnExistence_tblM2016 внутри spM2016_ChangePKConstraints_tblM2016, уже были экранированы вызовом quotename(). Внутри spM2016_CheckColumnExistence_tblM2016 я также выполняю qutoename() параметров в качестве проверки правильности имен таблиц, баз данных и схем. Этот код процедуры был опущен, и я приношу извинения за это.

По сути проблемная область находится здесь (проблемные параметры выделены **)

Код проблемы в spM2016_ChangePKConstraints_tblM2016

INSERT INTO @existing_cols EXEC spM2016_CheckColumnExistence_tblM2016 @col_names, **@tbl_name**,  **@db_name**, **@tbl_schema**, 1, 0;

Обратите внимание, что эти параметры, передаваемые в spM2016_CheckColumnExistence_tblM2016, уже экранированы с помощью quotename() выше в процедуре spM2016_ChangePKConstraints_tblM2016. А вот код отсутствующего ключа в процедуре spM2016_CheckColumnExistence_tblM2016:

Код проблемы в spM2016_CheckColumnExistence_tblM2016

ALTER PROCEDURE spM2016_CheckColumnExistence_tblM2016 
-- Add the parameters for the stored procedure here
@column_names   nvarchar(max) = N'M2016_Field_ID int', 
@table_name     nvarchar(max) = N'tblM2016_Fields',
@database_name  nvarchar(max) = N'MET',
@table_schema   nvarchar(max) = N'dbo',
@ExistFlag      int          = 1,                       -- Check for column existence in given table = 1
                                                        -- Check for column non-existence in given table = 0
@debug          int          = 0                    
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

 -- Insert statements for procedure here
DECLARE @tbl_name       nvarchar(max) = N'tblM2016_Fields',
        @col_names      nvarchar(max) = N'M2016_Field_ID',
        @tbl_schema     nvarchar(max) = N'dbo',
        @db_name        nvarchar(max) = N'MET',
        @tbl_full_name  nvarchar(max),
        @tbl_short_name nvarchar(max)

-- Insert statements for procedure here

***** PROBLEM STARTS HERE *****
SET @col_names = ISNULL(NULLIF(@column_names, ''), @col_names);
SET @tbl_name = quotename(ISNULL(NULLIF(@table_name, ''), @tbl_name));
SET @tbl_schema = quotename(ISNULL(NULLIF(@table_schema, ''), @tbl_schema));
SET @db_name = quotename(ISNULL(NULLIF(@database_name, ''), @db_name));

SET @tbl_full_name =  @db_name + '.' + @tbl_schema + '.' + @tbl_name;
SET @tbl_short_name = @tbl_schema + '.' + @tbl_name;

Я выделил проблему с ***** ПРОБЛЕМА НАЧИНАЕТСЯ ЗДЕСЬ ***** для ясности. Как видите, spM2016_CheckColumnExistence_tblM2016 также выполняет вызов quotename, чтобы убедиться, что @table_name, @table_schema и @database_name, переданные в spM2016_CheckColumnExistence_tblM2016, также имеют правильное экранирование. Но, поскольку я передал уже именованные @table_name, @table_schema и @database_name из spM2016_ChangePKConstraints_tblM2016 в spM2016_CheckColumnExistence_tblM2016 как @col_names, @tbl_name, @db_name, @tbl_schema, они оказались в двойных кавычках и, таким образом, были неузнаваемы при сравнении в запросах sys.object в spM2016_CheckColumnExistence_tblM2016 и поэтому возвращали фиктивные результаты.

Причина, по которой тестовый код работал, заключается в том, что параметры еще не были экранированы при передаче в spM2016_CheckColumnExistence_tblM2016, что привело к нормальному выполнению табличной переменной.

Оказывается, это не имело ничего общего с переменной таблицы :/

Jake 28.12.2016

Хранимая процедура не возвращает результаты из табличной переменной?

Ответы (2)

Похожие вопросы