CODEWITHSUNDEEP
sql-serversql-server-2008
Alex
Alex

Reputation: 1232

How do I run a query from a function, when the table name is passed in as argument

I am writing a function that would take table-name as argument. I know the column name I need out of the table, but the table name + table owner is dynamically passed in (well I need that to be the case, but it is currently not working).

It kind of looks like this:

CREATE FUNCTION [myowner].  
[alex_diff](@fromdate datetime, @todate datetime, @table_name varchar(256), @country_code varchar(3))  
RETURNS int  
AS  
BEGIN
...
set @date_string = (select calendar_month2 from ??? where calendar_year=@current_year and country_code = @country_code)
...
END

I couldn't put @table_name in place of '???' That gave me error:

Msg 1087, Level 16, State 1, Procedure alex_business_date_diff, Line 53 Must declare the table variable "@table_name".

What can I do to execute SQL on the dynamically passed table name?

I have tried doing the following:

    set @statement = 'select @output=' + @column_name + ' from ' + @table_name + ' where calendar_year=' + cast(@current_year as varchar(4)) + ' and country_code = ' + @country_code;
    EXEC sp_executesql @statement, N'@output varchar(31) OUTPUT', @date_string OUTPUT

But get this error (when I try to run the function):

Msg 557, Level 16, State 2, Line 1 Only functions and some extended stored procedures can be executed from within a function.

Upvotes: 0

Views: 1418

Answers (2)

Denis Valeev
Denis Valeev

Reputation: 6015

You can create a stored procedure with all your dynamic manipulations and consume the result via output parameter.

Upvotes: 1

JeffO
JeffO

Reputation: 8043

You could use CLR in SQL Server and take advantage of the .NET language which would run dynamic sql.

Upvotes: 1

Related Questions