passing tablename as parameter to function and to use it dynamically

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

Quote:

Originally Posted by satish@.entech.us

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

I don't think you can do this because DynamicSQL has it's own scope. Which means it won't return anything to the function. It will just run. So you can't do

SET @.Return = EXEC @.Command

As they are two different scopes.

What you could do is store the results of the output to a temp table and interrogate that when the function completes.

Cheers
C