function table joined with temp table

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)

function table joined with temp table

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2007-06-07 : 19:03:57

Is it possible??

I have a function table with three parameters (It selects the specific field I need from joins 3 tables and applies some bussiness rules to convert some values to the actual values I need). The function table works fine when using one set of parametes at the time
Declare @date1 datetime, @clientNo char(5), @clientDivision char(5)

Select @date1 = '6/1/2007', @clientNo = '00094', @clientDivision ='00002'
Select * From dbo.invoiceClientInfo (@date1, @clientNo, @clientDivision)

But I may have to call this table 1 to 3000+ times in one process and making it one by one with all the round trips will delay the process too much (I believe)

So I would like to find a way to retrieve all at once. I tried this code

select a.*, g.clientNo+g.clientDivision groupLocal 
from dbo.invoiceClientInfo (g.date1, g.clientNo, g.clientDivision) a cross join 
(Select '00094' clientNo, '00002' clientDivision, cast('20070601' as datetime) date1
Union Select '00094' clientNo, '00028' clientDivision, cast('20070601' as datetime) date1
Union Select '00094' clientNo, '00033' clientDivision, cast('20070601' as datetime) date1
Union Select '00094' clientNo, '00034' clientDivision, cast('20070601' as datetime) date1
Union Select '00094' clientNo, '09992' clientDivision, cast('20070601' as datetime) date1
Union Select '00416' clientNo, '00001' clientDivision, cast('20070601' as datetime) date1
Union Select '00416' clientNo, '00002' clientDivision, cast('20070601' as datetime) date1
Union Select '00416' clientNo, '00003' clientDivision, cast('20070601' as datetime) date1
Union Select '00416' clientNo, '00004' clientDivision, cast('20070601' as datetime) date1) G

quote:
I get this message when running on the QA
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Line 189
Line 189: Incorrect syntax near 'g'.

A candle loses nothing by lighting another candle

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-08 : 02:34:20

U cant pass the inputs to a table valued function which are from another table.
u need to loop through all the records one by one.

or change u r function into procedure.

If u post u r function code here any one will help u to change it into a procedure.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2007-06-08 : 08:58:47

Thanks PeterNeo, I will change it into a SP

A candle loses nothing by lighting another candle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-08 : 09:39:22

You can, if you work with SQL Server 2005 and use the CROSS APPLY method.

Peter Larsson
Helsingborg, Sweden

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2007-06-08 : 10:10:26

I created a user interface with a list view where the user is going to select from 1 to 3000+ lines.

I'm going to change the function tables on SQL to as SP with three results. But I'm not sure on how to send from VB all the requests (listView.checked) at once.

The only idea I have come to (to make only one round trip) is to loop through the list view and for each checked item create a select statement as the code above and then either

create a table or
pass the huge string as a parameter (with the risk of the string being too big for the SP to accept) or
pass a table as a parameter (if it is possible)

Does anyone have a better Idea (more efficient or stylish)

I'm more inclined to create/populate a table from VB with the items checked on the list view and then call the SP with no parameters that will use the table populated from VB to filter results. But I think there should be a better way to do it. Please let me know if you have a better idea.

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources