How to get a parameter entry to identify a table.column 2

[eo]

Hi,

I am writing a function. The function is based on a single table with 10 datetime columns. Lets call then DataTime1, DateTime2, ..., DateTime10.

The user will decide at runtime which Column to return, so I thought of parameterising the function to allow the user to select between DateTime1, ..., DateTime10 at runtime, and that will then return the corresponding Column. The actual function does all sorts of stuff, so this is simply a very simplistic view thereof, but you will get the idea.

ALTER FUNCTION [dbo].[fn_selectADateAndSomeFields]

(@SelectDate nvarchar(15))

RETURNS TABLE
AS
RETURN

SELECT @SelectDate, FieldA, FieldB, FieldC
  FROM dbo.Table
  ORDER BY @SelectDate Desc

GO

...The problem is that when the query is run...

select * from [dbo].[fn_selectADateAndSomeFields] ('DateTime4')

...then the Actual string is passed and I do not see...
DateTime4 (the actual datetime values), FieldA, FieldB, FieldC
...as expected

Is there anyway this can be done, otherwise I have to write 10 seperate functions, one for each possible option, and that just seems like a real waste


EO
Hertfordshire, England

 

[huggyd]

As far as I know you cannot "parameterise" select statements in this way. The options I tend to use in this scenario are
Either use 10 IF statements to return the correct column eg.

IF @SelectDate = 'DataTime1'
SELECT DataTime1, FieldA, FieldB, FieldC FROM dbo.Table ORDER BY DataTime1 Desc
IF @SelectDate = 'DataTime2'
SELECT DataTime2, FieldA, FieldB, FieldC FROM dbo.Table ORDER BY DataTime1 Desc
...

Or write dyanmic SQL that has the parameterised field names outside of the string

'SELECT ' + @SelectDate + ', FieldA, FieldB, FieldC FROM dbo.Table ORDER BY ' + @SelectDate + ' Desc

 

[Rofeu]

Take care that the dynamic SQL is highly vulnerable to sql injection attacks. Also, you run it with the EXEC or EXECUTE statement, e.g. EXEC 'dynamic ' + @SQL + ' string'

Cheers,

Rofeu

 

[gmmastros]

You cannot use dynamic sql within a function. Instead, you could hard code your column selection like this:

ALTER FUNCTION [dbo].[fn_selectADateAndSomeFields]

(@SelectDate nvarchar(15))

RETURNS TABLE
AS
RETURN

SELECT Case @SelectDate When 'FieldA' Then FieldA
                        When 'FieldB' Then FieldB
                        When 'FieldC' Then FieldC
                        End As SomeDateColumn
FROM   dbo.Table
ORDER BY @SelectDate Desc

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eo]

Thanks for all the responses.

I do normally steer clear of dynamic SQL. So I did end up following George's route, which is good to see validated by him.

Etienne

EO
Hertfordshire, England