Passing Temp Table Values into a UDF

This one is interesting...

Is there any way to pass a joined parameter into a UDF as I'm attempting below?

I have a temp table that I'm trying to create:

create table #t3
(bmkPerson int primary key,
LangCode nchar(5),
SName varchar(1000)
)
insert into #t3
select t2.bmkPerson, t2.LangCode,
select SName from SName_trans_udf(t2.LangCode)
from #t2 t2

Thanks in advance,

Renae
hilary321@.yahoo.comRenae (hilary321@.yahoo.com) writes:
> Is there any way to pass a joined parameter into a UDF as I'm attempting
> below?
> I have a temp table that I'm trying to create:
> create table #t3
> ( bmkPerson int primary key,
> LangCode nchar(5),
> SName varchar(1000)
> )
> insert into #t3
> select t2.bmkPerson, t2.LangCode,
> select SName from SName_trans_udf(t2.LangCode)
> from #t2 t2

No, you cannot pass a column value to a table-valued UDF. And when you
think of it it is logical: what result would you get? For each column
you get a whole table back. A scalar UDF works fine, because it's a
scalar value you need here.

SQL 2005 however does permit you to pass a column to a table-valued
UDF, but there is a special syntax for this. (Which I have not explored
yet, so I cannot give any examples.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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

Passing Table data type as Param to a function

Hi,
How can i pass Table data type parameter to UDF ?You can't but there's probably another solution (using joins for example).
If you need more help, describe the problem with DDL, sample data and
required results.
David Portas
SQL Server MVP
--|||A few methods to pass table data into a user defined function or stored
procedure:
1 - Use persistant tables or views. You pass the selection criteria as
variables. Requires you to define your datasets you would want to pass
before hand.
2 - Use a temporary table. The table and column names must be predefined
and the table must be built before calling the function.
3 - Use a text or varchar variable with delimiters to pass a single column
worth of data. Limit of 8000 characters.
4 - Use dynamic SQL. Security, speed and integrity problems arise with
dynamic SQL.
A few questions to ask before deciding what approach to take:
- What is the maximum number of rows to pass?
- Is more then one column required?
- Does the data being passed require indexing?
- Who will be using the funtion and what rights do they have?
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:%230w4L2RZFHA.1368@.tk2msftngp13.phx.gbl...
> Hi,
> How can i pass Table data type parameter to UDF ?|||Just to add to that, you can also send an XML doc:
http://msdn.microsoft.com/library/d... />
ql01c5.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tygrus" <tygrus@.user.net> wrote in message
news:e9YAVLcZFHA.3712@.TK2MSFTNGP09.phx.gbl...
A few methods to pass table data into a user defined function or stored
procedure:
1 - Use persistant tables or views. You pass the selection criteria as
variables. Requires you to define your datasets you would want to pass
before hand.
2 - Use a temporary table. The table and column names must be predefined
and the table must be built before calling the function.
3 - Use a text or varchar variable with delimiters to pass a single column
worth of data. Limit of 8000 characters.
4 - Use dynamic SQL. Security, speed and integrity problems arise with
dynamic SQL.
A few questions to ask before deciding what approach to take:
- What is the maximum number of rows to pass?
- Is more then one column required?
- Does the data being passed require indexing?
- Who will be using the funtion and what rights do they have?
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:%230w4L2RZFHA.1368@.tk2msftngp13.phx.gbl...
> Hi,
> How can i pass Table data type parameter to UDF ?