Необходимо объявить скалярную переменную @status Пользовательский тип таблицы

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

      ALTER PROCEDURE MyPROC
        @phoneNo nvarchar(30) = null,
        @status dbo.StatusViewTableType READONLY

        AS 
        BEGIN
     if(@option = 1)
    begin
      set @sql = 'SELECT   c.id,c.name,c.NewIc,c.OldIc,c.Title,c.nationality,c.CreatedDate,c.companyNo,c.NonIc,c.Email
                        FROM    customer c WITH (NOLOCK)
                        left outer join
                        dbo.Address with(nolock) on c.id =  dbo.Address.CustomerID 
                        left outer join dbo.ContactNo on c.id=dbo.ContactNo.CustomerID
                        WHERE     (c.id > 0) and ('


        if @oldIC <>'' and @phoneNo=''
            set @sql = @sql + ' (oldic ='''+ @oldIC + ''' )or'

        if @newIC <>'' and @phoneNo=''
            set @sql = @sql + ' (newIC ='''+ @newIC + ''' )or'

        if @companyno <>'' and @phoneNo=''
            set @sql = @sql + ' (companyno ='''+ @companyno + ''' )or'

        if @nonic <>'' and @phoneNo=''
            set @sql = @sql + ' (nonic ='''+ @nonic + ''' )or'

        If @phoneNo <>'' and @oldIC = '' and @newIC = '' and @companyno = '' and @nonic ='' 
        begin
            set @sql ='SELECT     c.id,c.name,c.NewIc,c.OldIc,c.Title,c.nationality,c.CreatedDate,c.companyNo,c.NonIc,c.Email
        FROM         dbo.Customer WITH (NOLOCK) c INNER JOIN
                              dbo.ContactNo WITH (NOLOCK)  ON c.id = dbo.ContactNo.CustomerID
        where   ContactNo = '''+ @phoneNo + ''' and  dbo.ContactNo.Contactability in (SELECT s.status   from  ' + [@status] + '  AS s ) '
        end
        if  @phoneNo='' 
        begin
        set @sql = Left(@sql,Len(@sql)-2)
        set @sql = @sql + ')'
        set @sql = @sql + '  and ( dbo.Address.Contactable in (SELECT  s.status   from ' + [@status] + '   AS s) or  dbo.ContactNo.Contactability in (SELECT  s.status from ' + [@status] + ' AS s) )'
        end 
print @sql

exec (@sql)
end

КОНЕЦ

В моем внутреннем коде я передаю параметры, как показано ниже:

SqlConnection myConnection = null;
SqlCommand myCommand = null;
SqlDataReader myReader = null;
myConnection = new SqlConnection(connectionString);
myCommand = myConnection.CreateCommand();
myCommand.CommandText = "GETCUSTOMER";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandTimeout = 0;
SqlParameter parameter;

int l = ids.FirstOrDefault().Length;
if (ids.FirstOrDefault().Length > 0)
{
    if (useDataTable)
    {
        parameter = myCommand.Parameters.AddWithValue("@status", CreateDataTable(ids));
    }
    else
    {
        parameter = myCommand.Parameters.AddWithValue("@status", CreateSqlDataRecords(ids));
    }

    parameter.SqlDbType = SqlDbType.Structured;
    parameter.TypeName = "dbo.StatusViewTableType";   
}

myCommand.Parameters.Add(new SqlParameter("@phoneNo", SqlDbType.NVarChar));    
myCommand.Parameters["@phoneNo"].Value = phoneNo;

У меня проблема с параметром @status. Он не распознается в хранимой процедуре.

Выдает исключение:

Необходимо объявить скалярную переменную "@status".

Ниже мой StatusViewTAbleType.

CREATE TYPE [dbo].[StatusViewTableType] AS TABLE(
    [Status] [int] NOT NULL
)
GO

Любая помощь будет оценена по достоинству.


sql-server stored-procedures user-defined-types
person Nuru Salihu    schedule 19.01.2015    source источник
comment
Пожалуйста, просмотрите все изменения, которые я внес в ваш пост, прежде чем вносить дальнейшие изменения. Всегда полезно представлять свои вопросы как можно лучше, иначе они не будут хорошо приняты. Я отменил ваше редактирование, так как потратил несколько минут на форматирование вопроса для вас, поэтому повторно примените ваши изменения в соответствии с моим редактированием.   -  person Tanner    schedule 19.01.2015
comment
Извините, сэр, мой запрос генерируется динамически. Как выше.   -  person Nuru Salihu    schedule 19.01.2015

Ответы (3)


arrow_upward
3
arrow_downward

Если вы используете табличное значение параметра в полном имени объекта, то вы должны заключить его в квадратные скобки, т.е. вместо @Status.status должно быть [@Status].status или использовать псевдоним.

Вы можете воссоздать это довольно просто:

DECLARE @P TABLE (ID INT);
SELECT @P.ID FROM @P;

Что выдает ошибку:

Необходимо объявить скалярную переменную @P.

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

ALTER PROCEDURE MyPROC
    @phoneNo nvarchar(30) = NULL,
    @status dbo.StatusViewTableType READONLY

AS 
BEGIN
    SELECT    * 
    FROM    dbo.Customer WITH (NOLOCK)  
            INNER JOIN dbo.ContactNo WITH (NOLOCK)  
                ON dbo.Customer.id = dbo.ContactNo.CustomerID
    WHERE   ContactNo = @phoneNo
    AND     dbo.ContactNo.Contactability IN (SELECT s.status FROM @status AS s);

END

Наконец, я бы настоятельно не советовал используя SELECT * в рабочем коде

ИЗМЕНИТЬ

Что касается вашей проблемы с динамическим SQL, используйте sp_executesql, что позволит вам использовать параметры, поэтому вместо чего-то вроде:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT A, B, C FROM T WHERE 1 = 1',
        @P NVARCHAR(10) = 'X';
IF @P <> '' 
    SET @SQL = @SQL + ' AND A = ''' + @P + '''';
    
EXEC(@SQL);

Вы бы использовали

DECLARE @SQL NVARCHAR(MAX) = 'SELECT A, B, C FROM T WHERE 1 = 1',
        @P NVARCHAR(10) = 'X';
IF @P <> '' 
    SET @SQL = @SQL + ' AND A = @P';
    
EXECUTE sp_executesql @SQL, N'@P NVARCHAR(10), @P;

Итак, вы передаете @P в качестве параметра запроса. Это должно решить все ваши проблемы с одинарными кавычками.

person GarethD    schedule 19.01.2015
comment
@ Г-н Гарет, пожалуйста, как мне добиться этого в строке sql, подобной той, что была в моей процедуре выше. Мне действительно тяжело с этим. - person Nuru Salihu; 19.01.2015
comment
@MR Гарет Похоже, sp_executesql то, что я ищу. однако, когда я запускаю его, он запрашивает параметр @statement, которого там нет. Пожалуйста, не могли бы вы помочь мне с моими параметрами .@id bigint = 0, @oldIC nvarchar(200) = '', @newIC nvarchar(200) = '', @companyno nvarchar(100) = '', @nonic nvarchar(100) = '', @phoneNo nvarchar(100) = '', @option int = 0, @sql nvarchar(1000) = null, @status dbo.StatusViewTableType READONLY .? - person Nuru Salihu; 19.01.2015

arrow_upward
0
arrow_downward

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

ALTER PROCEDURE Myproc @phoneNo NVARCHAR(30) = NULL,
                       @status  dbo.STATUSVIEWTABLETYPE READONLY
AS
  BEGIN
      SELECT *
      FROM   dbo.Customer WITH (NOLOCK)
             INNER JOIN dbo.ContactNo WITH (NOLOCK)
                     ON dbo.Customer.id = dbo.ContactNo.CustomerID
      WHERE  ContactNo = @phoneNo
             AND dbo.ContactNo.Contactability IN (SELECT status
                                                  FROM   @status)
  END
person Pரதீப்    schedule 19.01.2015
comment
Извините, сэр, за упоминание. Причина одинарных кавычек заключалась в том, что мой запрос создается динамически. Пожалуйста, посмотрите мой процесс еще раз. - person Nuru Salihu; 19.01.2015
comment
@NuruSalihu - Как упомянул garethD, вам нужно заключить @status в скобки, например ..select [@Status].status from.. - person Pரதீப்; 19.01.2015
comment
Хорошо, я пытаюсь сейчас. ТК - person Nuru Salihu; 19.01.2015
comment
Я получаю недопустимое имя столбца [@status]. Пожалуйста, я динамически генерирую строку запроса, а затем выполняю строку в конце. - person Nuru Salihu; 19.01.2015

arrow_upward
0
arrow_downward

Динамический SQL не распознает переменные вне его области. Вот почему он ошибается.

person SouravA    schedule 19.01.2015