working dynamicly with a table-valued function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

working dynamicly with a table-valued function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-24 : 07:42:58

hag writes "Does anyone know how to dynamicly pass a parameter to a Multi-statement table-valued function? without using a cursor?
for example:
if i want to get the store_id from the stores tables together with the table returned from a function when sending the same store_id.

i want to be able to get something like this:
1.select * from dbo.SalesByStore(select store_id from stores)
or
2. select store_id ,(select * from SalesByStore(store_id))
from stores

So the value store_id will be dynamicly passed to the function .

p.s: lets say this is the table-valued function
use pubs
go
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)

thanks,
hagit"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-24 : 09:11:56

Use Dynamic SQL..
HTH..

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT title, qty FROM sales s, titles t WHERE s.stor_id =' + @storeid + ' and t.title_id = s.title_id)

RETURN (EXEC(@SQL))

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-03-24 : 10:15:09

Hi Sekar,
I think UDF is very useful compare to Dynamic SQL. I gave some points to use Dynamic-SQL.

Why Use Dynamic SQL?
-------------------
You may want to use dynamic SQL when:
· You need all or part of the SQL statement to be generated during application execution.
· The objects referenced by the SQL statements do not exist at precompile time.
· You want the statement to always use the most optimal access path, based on current database statistics.
· You want to modify the compilation environment of the statement, that is, experiment with the special registers.

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources