CODEWITHSUNDEEP
t-sqlsql-server-2008stored-functions
Kanan Mehdizade
Kanan Mehdizade

Reputation: 73

CREATE FUNCTION failed because a column name is not specified for column 1. error for the Multiple parameter of function

Wanted to create the multiple parameter of function but it gives me this error:

CREATE FUNCTION failed because a column name is not specified for column 1.

Code below:

create function dmt.Impacted( 
@nameOfColumn varchar ,  @nameOfParam varchar)   
returns table   
as    
return  
(select  
case when '['+@nameOfColumn+']' is null or len(rtrim('['+@nameOfColumn+']')) = 0
then  Convert(nvarchar(2),0) 
else
@nameOfParam end from employee)  ;

Upvotes: 1

Views: 6687

Answers (2)

sticky bit
sticky bit

Reputation: 37472

As the error message clearly said, the column in the returned result need a name. Either give it an alias in the SELECT like

SELECT CASE
       ...
       END a_column_name
       ...

or define it in the declaration of the return type as in

...
RETURNS TABLE
        (a_column_name nvarchar(max)
...

As you can see in the second form you have to specify a data type. As your current code doesn't make much sense now I cannot figure out what is the right one there. You'd need to amend it.

Note, that len(rtrim('['+@nameOfColumn+']')) = 0 is never true as len(rtrim('['+@nameOfColumn+']')) is either NULL, when @nameOfColumn is NULL or at least 2 because of the added brackets.

If @nameOfColumn is supposed to be a column name you shouldn't use varchar (especially without a length specified for it) but sysname which is a special type for object names.

Either way you should define a length for @nameOfColumn and @nameOfParam as just varchar without any length means varchar(1), which is probably not what you want. And maybe instead of varchar you want nvarchar.

You may also want to look into quotename().

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Define name of column in SELECT statement :

(select case when '['+@nameOfColumn+']' is null or 
                   len(rtrim('['+@nameOfColumn+']')) = 0
             then  Convert(nvarchar(2),0)
             else  @nameOfParam 
        end as name_column -- define column name
 from employee)

Also, your function parameter has no data length, by default it will accept only 1 character @nameOfColumn varchar , @nameOfParam varchar & rest will trim.

Upvotes: 1

Related Questions