pass table name to sql function

Question

I have 2 function

frist find name of employee when Employee.id = id

CREATE FUNCTION FindEmployeeName
 (
    @id bit 
 )
RETURNS varchar(32)
AS
BEGIN
    declare @name varchar(32) 
    set @name=( SELECT Employee.Id FROM Employee WHERE Id=@id)
RETURN  @name
END

second find name of Doctor when Doctor.id = id :

CREATE FUNCTION FindDoctorName
 (
    @id bit 
 )
RETURNS varchar(32)
AS
BEGIN
    declare @name varchar(32) 
    set @name=( SELECT Doctor.Id FROM Doctor WHERE Id=@id)
RETURN  @name
END

Now, Can I have dynamic function ?

pass table and id to function and return name?

CREATE FUNCTION FindEmployeeName
 (
    @id bit ,
    @tbl varcahr(32)
 )
RETURNS varchar(32)
AS
BEGIN
    declare @name varchar(32) 
    set @name=( SELECT @tbl.Id FROM @tbl WHERE Id=@id)
RETURN  @name
END

Is there a better way?

Edited my schema is: enter image description here

You can but the performance is going to suck and would be a total kludge. The problem is you need to execute dynamic sql and in a function that makes it non-deterministic. There are some work arounds but honestly this type of process that can do everything from one piece of code is not a good approach. You wouldn't create a .net method that can find data from any object. — Sean Lange
– Sean Lange, Commented Aug 1, 2014 at 20:30
If anything I would create a "driver" type of procedure that could receive a parameter that lets you know which of your other procedures to execute. That is a LOT cleaner and the performance will not suffer. — Sean Lange
– Sean Lange, Commented Aug 1, 2014 at 20:31
Perhaps this issue is telling you that a Person table, super-type of both Doctor and Patient storing the columns common to both, is required in your schema. — Pieter Geerkens
– Pieter Geerkens, Commented Aug 1, 2014 at 20:46
@SeanLange How to write clean code? how to create "driver" procedure? — Amir Abdollahi
– Amir Abdollahi, Commented Aug 1, 2014 at 20:51

M.Ali · Accepted Answer · 2014-08-01 20:47:59Z

3

No you cannot pass a table name as variable to a User defined function as you will need dynamic sql to build the query and execute and this is something you cannot do inside a function, you will need to use a stored procedure for this .. something like this......

CREATE PROCEDURE FindEmployeeName
    @id INT ,   --<-- I dont think you need BIT here, BIT can only 1 or 0
    @tbl sysname,
    @Name varchar(32) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;

   DECLARE @SQL NVARCHAR(MAX);

  SET @SQL =  N' SELECT @Name = Name FROM ' + QUOTENAME(@tbl)
            + N' WHERE Id = @id '
 EXECUTE sp_executesql @SQL 
                      ,N'@id INT , @Name varchar(32) OUTPUT'
                      ,@Id
                      ,@Name OUTPUT
END

answered Aug 1, 2014 at 20:47

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

Comments

Sean Lange · Accepted Answer · 2014-08-01 20:56:30Z

1

I would do this without dynamic sql if at all possible. Here is one way to do that. Basically you would end up with three procedures but the performance is well worth it. Take a look at this article which explores the performance benefits of this type of approach. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

create procedure GetDoctorData
(
    @Id int
) as
    select Doctor.Id FROM Doctor WHERE Id=@id

go

create procedure GetEmployeeData
(
    @Id int
) as
    select Employee.Id FROM Employee WHERE Id=@id

go

create procedure GetPersonData
(
    @PersonType varchar(10)
    , @Id int
) as

    if @PersonType = 'Doctor'
        exec GetDoctorData @Id      

    if @PersonType = 'Employee'
        exec GetEmployeeData @Id

go

answered Aug 1, 2014 at 20:56

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

6 Comments

Fuck StackOverflow Over a year ago

Excellent point about the performance benefit with the three procs. This avoids the parameter sniffing issue. Also, an excellent article by Gail Shaw.

Fuck StackOverflow Over a year ago

Another thought: could the OP take your strategy and use three UDF's instead of three SP's?

Gouri Shankar Aechoor Over a year ago

Dynamic sql when called using particularized sp_executesql is cached in the execution plan so you do not have to create additional objects such as SP's in fear of performance

Sean Lange Over a year ago

@GouriShankarAechoor it is true that dynamic sql is cached. You should read the article I posted. Gail goes into great detail about how this caching can adversely affect performance in situations like this. The basic gist is that with different parameters you might need a different execution plan.

Gouri Shankar Aechoor Over a year ago

@SeanLange, scenario based yes but I do not see the number of tables being huge in this scenario( 3 variants ) so I would imagine it should be fine. However, please can you add the link to article so I can gain some information on it. Appreciate it. Thanks.

|

Dipon Roy · Accepted Answer · 2014-08-01 20:54:10Z

0

DECLARE @id INT;                            
DECLARE @tblName VARCHAR(100);
SET @id = 1;
SET @tblName = 'Employee'

DECLARE @sqlString VARCHAR(500);
SET @sqlString = 'SELECT *'
                    + ' FROM ' +@tblName
                    + ' WHERE Id = ' + CAST(@id AS VARCHAR(100));
--PRINT(@sqlString);
EXEC (@sqlString);

i have only demonstrated it to show how we can turn string to sql query. But take the suggestion as 'Sean Lange' pointed.

answered Aug 1, 2014 at 20:54

Dipon Roy

4261 gold badge6 silver badges19 bronze badges

2 Comments

M.Ali Over a year ago

This method is dangerous, vulnerable to sql injection attack.

Dipon Roy Over a year ago

did n't i mention "i have only demonstrated it to show how we can turn string to sql query"

Collectives™ on Stack Overflow

pass table name to sql function

3 Answers 3

Comments

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related