SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 calling a function		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/01/2006 :  22:41:45  Show Profile  Reply with Quote
Hi friends
i've a function that takes a parameter as input and returns a table
if i call my function like below it works as expected

select * from dbo.myfunc(1)
my question is can this input be SQL ?
i mean i tried following but getting error "Incorrect syntax near the keyword 'select'."

select * from dbo.myfunc(select id from sometable)
any ideas on this one please?
Thanks

Cheers
Edited by - rajani on 10/01/2006 22:42:24

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/01/2006 :  22:45:44  Show Profile  Reply with Quote
You cannot do that.

The input parametr for a table valued function must be a local variable or a constant. It cannot be a query result set.





CODO ERGO SUM
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/01/2006 :  22:50:52  Show Profile  Reply with Quote
Thanks for that Michael.
thats what i just found here "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_73lf.asp"

but is there any way achieve what i want here?
Thanks for ur time

Cheers
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 10/01/2006 :  23:12:09  Show Profile  Reply with Quote
quote:
Originally posted by rajani
...but is there any way achieve what i want here?...

You haven't really explained what you want.

If what you want is to use a result set as the input parameter to a table valued function, then the answer is no.




CODO ERGO SUM
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/01/2006 :  23:22:14  Show Profile  Reply with Quote
Michael
actually we've a generic function that returns a table.i needed same functionality in a SQL am writing and thought i could use this function instead of duplicating same code.
looks like i need to create a view here.

Cheers
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16745 Posts
Posted - 10/02/2006 :  00:46:30  Show Profile  Reply with Quote
quote:
select * from dbo.myfunc(select id from sometable)


if the select id from sometable is returning one record, then you can just assign it to a variable and pass the variable into the function

select @ID = id from sometable
select * from dbo.myfucc(@ID)


KH
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/02/2006 :  00:51:06  Show Profile  Reply with Quote
nah!
it will return more than 1 record so cant use it here.

Cheers
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/02/2006 :  08:47:36  Show Profile  Reply with Quote
Could you concatenate all the ID values as a comma delimited list, and then pass that as a parameter to your function (and then have it split the list and JOIN that to whatever it is doing with the IDs)?

Kristen
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/02/2006 :  15:48:10  Show Profile  Reply with Quote
thats what i thought to do initially but finally ended up doing a view.
Thanks Kristen

Cheers
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000