Pass in field name and/or table name as parameter to function/procedur

[infinitizon]

Ok, I dont know How to explain this but I hope someone will understand.
I have a table called intGeneratorTable like this

ForJobs ForVisitors
24 NULL

Now I want to create a function like so:

CREATE FUNCTION [dbo].[GetForJobs](
@strFieldName varchar(255),
@strTableName varchar(255)
)
returns int
AS
BEGIN
return SELECT @strFieldName FROM @strTableName
END

So that I can call the function like so

select [dbo].[GetForJobs]('ForJobs', 'intGeneratorTable')

But it is not working.

People say it can be done but not like that but no one has giving me any working solution.

I need help


____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[gmmastros]

I understand what you are trying to do, but it cannot be done unless you are willing to hard code a bunch of data, which is not a good idea.

Let me explain...

Normally, when you want to use column names and table names that are in a variable, your best option is to use dynamic SQL. Unfortunately, you cannot use dynamic sql in a function because function code must be deterministic. This really means that for a given input, the output is always the same. There is no guarantee of this when using dynamic sql, so SQL Server does not allow it.

Think about it this way...

When you execute a query, SQL Server must determine what information you want and where to get the information from. SQL Server also tries to do this as quickly as possible so that your query will execute as fast as possible. Functions are compiled in to the database, and the execution plan is stored for it. SQL Server cannot store the execution plan for a stored procedure because the execution plan could change every time you run the code.

You can use dynamic sql with a stored procedure, but the way you call the stored procedure would not be the same. What I mean is, you can execute a function for each row that is returned by a query, but you cannot do this for a stored procedure.

You could write the function another way, but it would require a lot of hard-coding table and column names. For example:

CREATE FUNCTION [dbo].[GetForJobs](
 @strFieldName varchar(255),
 @strTableName varchar(255)
 ) 
returns int
 AS 
BEGIN 
  Declare @Output Int

  If @strTableName = 'intGeneratorTable' 
    Begin
      If @strFieldName = 'ForJobs'
        SELECT @Output = ForJobs FROM intGeneratorTable
      Else If @strFieldName = 'ForVisitors'
        SELECT @Output = ForVisitors FROM intGeneratorTable
    End

  Return @Output
END

Notice all the hardcoded table and column names. I would strongly advise against using code like this because it will be nearly impossible to maintain.

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

 

[infinitizon]

Thanks George,
I saw this kind of code while google searching and I realised how difficult it may be to maintain.

I was hoping a simpler method will suffice.

Thanks.

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.