SQL server Function - Take column name as input parameter

Question

I need to write a SQL function to return column specific values, so I am passing the column name as a parameter to SQL-function to return its corresponding value. Here is the sample function

CREATE FUNCTION GETDATETIME(@columnName VARCHAR(100)) 
RETURNS DATETIME
AS 
BEGIN
    RETURN (SELECT TOP 1.@columnName  FROM TEST_TABLE ) 
END
GO

The above function seems to be straight forward, but it not working as expected. And when I execute the function

SELECT dbo.GETDATETIME('DATETIMECOLUMNNAME')

I am getting this error:

Conversion failed when converting date and/or time from character string.

Can someone help me to identify the issue?

You can't dynamically specify the column, your query is attempting to return 'DATETIMECOLUMNNAME' which is of course not a datetime. You can't do what you want to do in a function because you need dynamic SQL which can only be run from inside an SP. And that aside its a bad design approach to be using dynamic columns. Final point you have a "." (dot) between your 1 and @columnname which isn't valid syntax either. — Dale K
– Dale K, Commented Jul 26, 2019 at 6:01

Shakeer Mirza · Accepted Answer · 2019-07-26 07:19:18Z

2

For that you need to write dynamic sql. But Functions won't support execute statement.

So you need to write multiple If conditions for each column.

CREATE FUNCTION GETDATETIME(@columnName VARCHAR(100)) 
RETURNS DATETIME
AS 
BEGIN
   DECLARE @RESULT DATETIME;
    IF (@columnName = 'ABC')
    Begin
        SELECT TOP 1 @RESULT  = [ABC]  FROM TEST_TABLE
    END
    ELSE IF (@columnName = 'DEF')
    Begin
        SELECT TOP 1 @RESULT  = [DEF]  FROM TEST_TABLE
    END
    ELSE IF (@columnName = 'GHI')
    Begin
        SELECT TOP 1 @RESULT = [GHI]  FROM TEST_TABLE
    END

    RETURN  @RESULT
END
GO

Edit 2: If your column always return Datetime, then you can do like below.

CREATE TABLE A_DUM (ID INT, STARTDATE DATETIME, ENDDATE DATETIME, MIDDLEDATE DATETIME)

INSERT INTO A_DUM 
SELECT 1, '2019-07-24 11:35:58.910', '2019-07-28 11:35:58.910', '2019-07-26 11:35:58.910'
UNION ALL
SELECT 2, '2019-07-29 11:35:58.910', '2019-08-01 11:35:58.910', '2019-07-24 11:35:58.910'

And your function like below

CREATE FUNCTION GETDATETIME(@columnName VARCHAR(100)) 
RETURNS DATETIME
AS 
BEGIN

DECLARE @RESULT DATETIME;

SELECT TOP 1 @RESULT = CAST(PROP AS DATETIME)
FROM A_DUM
UNPIVOT
(
PROP FOR VAL IN (STARTDATE, ENDDATE,MIDDLEDATE)
)UP

WHERE VAL = @columnName

    RETURN @RESULT 
END
GO

edited Jul 26, 2019 at 7:19

answered Jul 26, 2019 at 6:03

Shakeer Mirza

5,1182 gold badges20 silver badges42 bronze badges

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

3 Comments

DarkRob Over a year ago

giving error: Msg 241, Level 16, State 1, Line 508 Conversion failed when converting date and/or time from character string.

Suganthan Madhavan Pillai Over a year ago

This helped me in some way

Shakeer Mirza Over a year ago

Edited. Initially I didn't tested it. Thanks @DarkRob

Coruscate5 · Accepted Answer · 2022-01-21 02:52:14Z

There's a workaround to this, similar to @Shakeer's answer - if you are attempting to GROUP BY or perform a WHERE on a column name, then you can just use a CASE statement to create a clause to match on specific column names (if you know them).

Obviously this doesn't work very well if you have many columns to hard-code, but at least it's a way to achieve the general idea.

E.g. with WHERE clause:

WHERE
(CASE 
  WHEN @columnname = 'FirstColumn' THEN FirstColumnCondition
  WHEN @columnname = 'SecondColumn' THEN SecondColumnCondition
  ELSE SomeOtherColumnCondition
  END)

Or with GROUP BY:

GROUP BY
(CASE 
  WHEN @columnname = 'FirstColumn' THEN FirstColumnGroup
  WHEN @columnname = 'SecondColumn' THEN SecondColumnGroup
  ELSE SomeOtherColumnGroup
  END)

DarkRob · Accepted Answer · 2019-07-26 06:10:06Z

1

No you cannot use dynamic sql in functions in SQL. Please check this link for more info link.

So it is not possible to achieve this by any function, yes you may use stored procedures with output parameter for same.

You may find this link for reference link.

answered Jul 26, 2019 at 6:10

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL server Function - Take column name as input parameter

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related