Невозможно передать динамические параметры для сортировки нескольких столбцов в таблице базы данных

Это моя хранимая процедура

 [sp_Get_Data]
   (
   @IdParam INT, 
   @OrderBy varchar(50),
   @Direc varchar(4),
   )
 AS
 BEGIN
select 
   [LogID] //int
  ,[Address]   //varchar
  ,[State]    //varchar
  ,[City]
  ,[Zip]
  FROM LogData 
WHERE IdParam = LogID
order by 

  CASE @Direc 
    WHEN 'desc' THEN  
    CASE @OrderBy 
        WHEN 'LogID' THEN LogID 
        WHEN 'Address' THEN Address
        END 
    END 
    DESC, 
CASE @Direc 
    WHEN 'asc' THEN              
    CASE @OrderBy 
        WHEN 'LogID' THEN LogID 
        WHEN 'Address' THEN Address
        END 
    END 

EXEC [sp_Get_Data] @IdParam ='..' @OrderBy = 'LogID', @Direc = 'asc'   // WORKS FINE
EXEC [sp_Get_Data] @IdParam ='..' @OrderBy = 'Address', @Direc = 'asc'

Ошибка:

Не удалось выполнить преобразование при преобразовании значения varchar 'xyz' в тип данных int.

Вместо использования этих динамических параметров для сортировки, если я передам

ORDER BY Address ASC ....

это работает.

Есть ли что-то очень простое, что мне здесь не хватает?


sql parameter-passing stored-procedures sql-server-2008
person Scorpio    schedule 24.01.2013    source источник
comment
Я удаляю jquery. Кстати, лучше, если вы правильно отформатируете свой код   -  person Alexander    schedule 24.01.2013
comment
Можем ли мы увидеть остальную часть кода? Весь sp_get_data был бы хорош   -  person Urban Björkman    schedule 24.01.2013
comment
@ Urban Björkman Он добавил соответствующий код, и для примечания я также передаю IdParam в операторе exec ...   -  person Scorpio    schedule 24.01.2013
comment
Примечание: вы не должны не использовать префикс sp_ для своих хранимых процедур. Microsoft зарезервировала этот префикс для собственного использования, и вы рискуете столкнуться с конфликтом имен в будущем. Лучше просто избегать sp_ и использовать в качестве префикса что-то другое - или вообще не использовать префикс!   -  person marc_s    schedule 25.01.2013

Ответы (2)


arrow_upward
1
arrow_downward

Проблема в том, что ваше первое выполнение (с использованием LogID) сгенерировало план выполнения с использованием INT. При выполнении второго вызова в предварительно скомпилированном плане выполнения возникла исключительная ситуация преобразования данных.

Одним из решений было бы привести LogID к типу VARCHAR, чтобы всегда сталкиваться с одним и тем же типом данных:

(...)

CASE @Direc 
WHEN 'desc' THEN  
CASE @OrderBy 
    WHEN 'LogID' THEN CONVERT(VARCHAR(50), LogID)
    WHEN 'Address' THEN Address
    END 
END 
DESC, 
CASE @Direc 
WHEN 'asc' THEN              
CASE @OrderBy 
    WHEN 'LogID' THEN CONVERT(VARCHAR(50), LogID)
    WHEN 'Address' THEN Address
    END 
END 

(...)

Но обычно такие операции (ORDER BY) выполняются на стороне приложения... если вы не можете оправдать такой метод значительным улучшением производительности. Я просто хочу убедиться, что ты знаешь, что делаешь.

Примечание. Вы можете попробовать использовать OPTION (RECOMPILE) в конце хранимой процедуры и не использовать CONVERT, так как план выполнения будет перекомпилироваться после каждого использования. Было бы проще перекомпилировать хранимую процедуру после каждого выполнения вместо использования всех этих CONVERT/RIGHT/REPLICATE настроек. Вот увидишь.

person Francis P    schedule 24.01.2013
comment
Спасибо ... сделал мой день ... можете ли вы сказать мне или направить мне любую статью, которая подробно объясняет причину такого поведения ... просто хотел понять это лучше ... - person Scorpio; 24.01.2013
comment
Вы можете увидеть это сообщение SQLAuthority: blog.sqlauthority.com/2007/07/17/, дополнительную информацию можно найти в комментариях. - person Francis P; 24.01.2013
comment
@Scorpio: прочитайте мою заметку, вы не получите ожидаемого заказа после преобразования int в varchar. int orders 1,2,11 но after converting to varchar будет order as 1,11,2 - person Kaf; 24.01.2013

arrow_upward
2
arrow_downward

Тип возвращаемого значения CASE должен быть таким же. В первом случае он не достигает адреса, поэтому он работает. Но для «Адрес» ожидается, что он будет таким же, как LogId, который является целым числом. Таким образом, преобразование не удается.

ПРИМЕЧАНИЕ. Преобразование LogId в тип VARCHAR приведет к сортировке ошибок but it would not give you correct ordering в виде чисел. Так что используйте функцию RIGHT, чтобы справиться с этим, как

RIGHT(REPLICATE('0',11) + CONVERT(VARCHAR(50),LogID), 12)

(Вот ДЕМО)

SELECT * FROM T
ORDER BY 
   CASE @Direc 
    WHEN 'desc' THEN  
    CASE @OrderBy 
        WHEN 'LogID' THEN RIGHT (REPLICATE('0',11) + CONVERT(VARCHAR(50),LogID), 12)
        WHEN 'Address' THEN Address
        END 
    END 
    DESC, 
CASE @Direc 
    WHEN 'asc' THEN              
    CASE @OrderBy 
        WHEN 'LogID' THEN RIGHT (REPLICATE('0',11) + CONVERT(VARCHAR(50),LogID), 12)
        WHEN 'Address' THEN Address
        END 
    END
person Kaf    schedule 24.01.2013