CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-function
Amir Abdollahi
Amir Abdollahi

Reputation: 455

pass table name to sql function

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

Upvotes: 0

Views: 19500

Answers (3)

Sean Lange
Sean Lange

Reputation: 33580

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

Upvotes: 1

Dipon Roy
Dipon Roy

Reputation: 406

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.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69564

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

Upvotes: 3

Related Questions