calling a function - SQL Server Forums

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

Transact-SQL (2000)

calling a function

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-01 : 22:41:45

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-01 : 22:45:44

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

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-01 : 22:50:52

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-01 : 23:12:09

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

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-01 : 23:22:14

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-02 : 00:46:30

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)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-02 : 00:51:06

nah!
it will return more than 1 record so cant use it here.

Cheers

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 08:47:36

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

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-02 : 15:48:10

thats what i thought to do initially but finally ended up doing a view.
Thanks Kristen

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources