динамический sql в хранимой процедуре с именем столбца в качестве входного параметра

У меня есть эта хранимая процедура, которая принимает имя столбца в качестве параметра ввода. Оператор SELECT выберет столбец в соответствии с входным параметром.

create procedure getColumn (@whichColumn varchar)
as
begin
    declare @sql nvarchar(max) 
    set @sql = 'SELECT [' + @whichColumn + ']' 
        + ' FROM myTable'
        + ' where ['+ @whichColumn + '] is not null'
        + ' and [' + @whichColumn + '] != '''' ' ;
    exec sp_executesql @sql
end

Когда я выполняю эту хранимую процедуру,

exec getColumn 'Apple';

ошибка говорит "Недопустимое имя столбца "A"". Я не понимаю, почему он получает только первый символ ввода


sql-server stored-procedures tsql dynamic-sql
person kaboom    schedule 11.03.2014    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Изменить эту строку

create procedure getClumn (@whichColumn varchar)

to 

create procedure getClumn (@whichColumn varchar(max))

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

person Pragnesh Khalas    schedule 11.03.2014
comment
Вау, давайте не будем увлекаться и перейдем к VARCHAR(MAX) моему предположению, что ему, вероятно, потребуется менее 8000 символов, чтобы выполнить это. - person Zane; 11.03.2014
comment
@Zane Я предполагаю, что ему никогда не понадобится больше 128 :) - person M.Ali; 11.03.2014

arrow_upward
7
arrow_downward

Проверьте объявление параметра:

@whichColumn varchar

Из MSDN:

Когда n не указано в операторе определения данных или объявления переменной, длина по умолчанию равна 1.

Итак, это однобуквенное varchar. Попробуйте указать размер:

@whichColumn varchar(50)

Или, что еще лучше, используйте системный тип для имен объектов:

@whichColumn sysname
person Andomar    schedule 11.03.2014
comment
^^^^ Это ваш ответ. Всегда объявляйте длину ваших столбцов. - person Zane; 11.03.2014
comment
Я думал, что это 30, если длина не была упомянута: S - person M.Ali; 11.03.2014
comment
@M.Ali: Это правда, если вы не укажете длину в cast и convert. Но в объявлениях переменных длина по умолчанию равна единице. Вы можете проголосовать здесь, чтобы исправить это безумие. - person Andomar; 11.03.2014
comment
опсс перепутал. :) Я пытался проголосовать, но это не позволяет мне попробовать это позже. - person M.Ali; 11.03.2014
comment
/sigh беспокоит тот факт, что это не принятый ответ. - person Zane; 11.03.2014

arrow_upward
3
arrow_downward 

create procedure getColumn (@whichColumn nvarchar(128))   --<-- Maximum column name lenght
as
begin
    declare @sql nvarchar(max); 
    set @sql =   N'SELECT ' + QUOTENAME(@whichColumn)+ N' FROM myTable'
               + N' where '+ QUOTENAME(@whichColumn) + N' is not null'
               + N' and ' + QUOTENAME(@whichColumn)  +  N' != '''' ' ;
    exec sp_executesql @sql
end

Кстати, использование квадратных скобок в объединяемой строке — это не то же самое, что использование функции QUOTENAME.

person M.Ali    schedule 11.03.2014
comment
Хотите оставить комментарий тому, кто проголосовал против? - person M.Ali; 11.03.2014
comment
+1 к дисбалансу. Не уверен, почему вы получили DV либо. - person Zane; 11.03.2014
comment
Ура, Зейн, есть несколько болванов, которые голосуют ради развлечения. Я бы хотел, чтобы существовала система, согласно которой каждый раз, когда кто-то несправедливо голосовал против, его репутация снижалась на 100 :S - person M.Ali; 11.03.2014
comment
М. Али Я не знал о ключевом слове TSQL QUOTENAME, пока не прочитал предложенный вами ответ. Это решило проблему, с которой я писал динамический SQL для установки имени столбца в хранимой процедуре UPDATE. Бонус в том, что чище писать и понимать ИМХО. - person Doreen; 25.03.2019