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.
| Author |
Topic |
|
micho81
Starting Member
3 Posts |
 Posted - 2005-09-24 : 08:13:46
|
| Hello,I'd like to know if it is possible to use the output of function in subquery while passing parameters from the main select. E.g.:SELECT * FROM my_table1 WHERE my_table1.ID IN ( SELECT ID FROM my_function( myTable1.ID))"my_function" returns milti-line table of IDs.Works fine only if I specify the "ID":SELECT * FROM my_table1 WHERE my_table1.ID IN ( SELECT ID FROM my_function( 'ALFA_BETA'))Thank you. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-25 : 02:34:07
|
| Nope, I don't think you can do that.I repeat the JOIN/WHERE from the main statement in my_function() to constrain it.Kristen |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-09-25 : 03:56:43
|
| Hi,I think you can do this. It can be done using Scalar function. This will return just one value. You can pass value to this function and it will return just one value.Check BOL for more detail.RegardsSachin Samuel |
|
|
|
micho81
Starting Member
3 Posts |
Posted - 2005-09-25 : 04:27:52
|
quote:
Originally posted by sachinsamuel
Hi,I think you can do this. It can be done using Scalar function. This will return just one value. You can pass value to this function and it will return just one value.Check BOL for more detail.RegardsSachin Samuel
Hi,The problem is, that I need to test a set of IDs. I explain. I have a table that includes all customer IDs, but some of the customers are marked like "inactive". An active customer can have the same name as an inactive, but different ID [e.g. he has moved into another location, billing address has been changed]. So I have this set of active IDs that can have continuity on inactive IDs or not. It is needed because an "inactive" ID that active is carrying on can have invoices that have not been payed etc. So I pass ID of an active customer to this function and it returns ID of this active custumer and all IDs of inactive customers he is carying on.I tried to create a scallar function that returns the set of IDs seperated with commas: 'ID1','ID2','ID2'. But this doesn't work with " test_ID IN ( my_function( ID))"... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-25 : 18:13:34
|
| The only way that would work the way you have the function currently setup is with dynamic SQL. You could have a table fuction that returns all the valid ID's though and just join to that in the FROM clause.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-26 : 01:12:54
|
| On second thoughts you might be able to use OPENQUERY (or some other ADO-like query mechanism) in your Function to do what you want. But I expect you'd be better trying to avoid that!Kristen |
|
|
|
micho81
Starting Member
3 Posts |
Posted - 2005-09-29 : 03:53:10
|
Thanks to all of you for your posts. They really helped me to handle this problem...Se you again by my next problem  |
|
|
|
|
|
|
|
|
Help with using UDF