dynamic sql in stored procedure with column name as input parameter

Question

I have this stored procedure that accept a comlumn name as inptu parameter. The SELECT statement will select a column according to input parameter

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

When I execute this stored procedure,

exec getColumn 'Apple';

the error says "Invalid column name 'A' " . I cannot see why it only gets the first character of the input

Andomar · Accepted Answer · 2014-03-11 17:35:41Z

7

Check out your parameter declaration:

@whichColumn varchar

From MSDN:

When n is not specified in a data definition or variable declaration statement, the default length is 1.

So that's a single-letter varchar. Try to specify a size:

@whichColumn varchar(50)

Or even better, use the system-defined type for object names:

@whichColumn sysname

answered Mar 11, 2014 at 17:35

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zane Over a year ago

^^^^This is your answer. Always declare the length of your columns.

M.Ali Over a year ago

I thought it was 30 if no length has been mentioned :S

Andomar Over a year ago

@M.Ali: That's true if you omit the length in cast and convert. But in variable declarations, the default length is one. You can vote here to fix the madness.

M.Ali Over a year ago

opss got it mixed. :) I have tried to vote but it wont let me will try it later.

Zane Over a year ago

/sigh the fact that this isn't the accepted answer is troubling.

M.Ali · Accepted Answer · 2014-03-11 18:11:17Z

3

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

On a side note Using square brackets in you concatinating string isnt the same as using QUOTENAME function.

edited Mar 11, 2014 at 18:11

answered Mar 11, 2014 at 18:02

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

4 Comments

M.Ali Over a year ago

DO you care to leave a comment whoever has down voted ?

Zane Over a year ago

+1 to off balance. Not sure why you got the DV either.

M.Ali Over a year ago

Cheers Zane there are some numpties out there down voting for fun. I wish there was a system in place that every time some down voted unfairly it would -100 their reputation :S

Doreen Over a year ago

M. Ali I did not know about TSQL keyword QUOTENAME until I read your suggested answer. It solved the issue I was having writing dynamic SQL to set a column name in an UPDATE stored procedure. A bonus is it's cleaner to write and understand IMHO.

Pragnesh Khalas · Accepted Answer · 2014-03-11 17:34:48Z

1

Change this line

create procedure getClumn (@whichColumn varchar)

to

create procedure getClumn (@whichColumn varchar(max))

because if you are not assign size of varchar at that time it consider only one character so it get only one character A and generate error.

answered Mar 11, 2014 at 17:34

Pragnesh Khalas

2,8982 gold badges15 silver badges26 bronze badges

2 Comments

Zane Over a year ago

Whoa let's not get carried away and go all the way to VARCHAR(MAX) my assumption is he probably needs less than 8,000 characters to accomplish this.

M.Ali Over a year ago

@Zane My assumption is hes never going to need more than 128 :)

Collectives™ on Stack Overflow

dynamic sql in stored procedure with column name as input parameter

3 Answers 3

5 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related