Get Parameter as Column Name in SQL Function

Question

I wrote a function that will be passed 1 string parameter. Inside that function it is supposed to get that string as you can see but its not working because parameter I transfer is string. The receiver of parameter suppose to get column name if I write column name as its format it works fine but I need to get passed.

This is the string I pass:

[Where], [University], [BeginDate], [GraduateDate], [Major]

Now this is function

DROP FUNCTION NewTable
GO
CREATE FUNCTION NewTable
     (@PassParameter NVARCHAR(MAX))
RETURNS TABLE 
AS
    RETURN  
        SELECT * 
        FROM 
            (SELECT O.RowIndex, O.OptionValue, T.TypeValue
             FROM Options O
             LEFT OUTER JOIN Types T ON T.TypeID = O.TypeID
             GROUP BY O.RowIndex, O.OptionValue, T.TypeValue) d
        PIVOT
        (   
            MAX(OptionValue)
            FOR [TypeValue] IN (@PassParameter) <-- this is tricky part this will not work but I put this [Where], [University], [BeginDate], [GraduateDate], [Major] this is gonna work
        ) PIV

GO

Also I can't use a stored procedure because using a function is my requirement.

The way to accomplish your query requires dynamic SQL. However you can't execute dynamic SQL from within a function. So it looks like you'll have to modify your requirements. — Dale K, May 21 '19 at 02:41

score 0 · Answer 1 · answered May 21 '19 at 04:38

You cannot have dynamic sql within a user defined function. If you want to have this kind of functionality, you can define a CLR user defined function.

Instead, You can define a stored procedure with dynamic sql and INSERT the output of stored procedure into the tabletype.

I have given sample for your reference.

First defined a user defined table type

CREATE TYPE RowIndex as
Table
(
RowIndex INT,
optionValue VARCHAR(20),
TypeValue VARCHAR(20)
);

Next define stored procedure with dynamic sql content.

CREATE PROCEDURE NewTable
     (@PassParameter NVARCHAR(MAX))
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 
    '
             SELECT * 
        FROM 
            (SELECT O.RowIndex, O.OptionValue, T.TypeValue
             FROM Options O
             LEFT OUTER JOIN Types T ON T.TypeID = O.TypeID
             GROUP BY O.RowIndex, O.OptionValue, T.TypeValue) d
        PIVOT
        (   
            MAX(OptionValue)
            FOR [TypeValue] IN '+ @PassParameter +' 
        ) PIV

            '
        EXEC sp_Executesql @sql
END
GO

Now, Insert the output of the stored procedure into the table type.

DECLARE @RowIndex RowIndex
INSERT INTO @RowIndex
EXEC NewTable 'Test'
SELECT * FROM @RowIndex

Get Parameter as Column Name in SQL Function

1 Answers1