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 |
|
mr_mist
Grunnio
1870 Posts |
 Posted - 2003-07-07 : 10:42:53
|
| I have a function that I can use to show a customer's latest payment and the week in which that payment was made. I can call it like thisSELECT * from dbo.show_latest_week_for_customer_payment (156,'Z') Where 156 is the customer number and Z is an account type.What I would like to do is thisSELECT * from dbo.show_latest_week_for_customer_payment ((select customer_id_nos from someothertable),'Z') but it will not let me. This is not the correct syntax apparently.Previously, the code was two separate functions that returned a single value each, they could be used likeSELECT dbo.show_latest_week (customer_id,'z'), dbo.show_latest_payment (customer_id,'z')FROM someothertablebut now that the function returns a TABLE I can no longer do this, SQL just says invalid object dbo.show_latest_week_for_customer_payment.So, I guess what I am asking is how do I present a set of values to a function that returns a table result set?-------Moo. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-07 : 11:13:03
|
| Try reading through this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830it shows how to pass a comma-seperated string to a function.The other way to have functions handle entire sets of data (w/mutliple columns, which is not easy with a CSV) is to have a "middleman" set up tables that you fill up, and then call the function:FunctionHeaders---------------SetNumber int indentityFunctionDetails---------------Setnumber intCol1Col2Col3 ... etc ....And you insert a row into the FunctionHeaders table, and get back a SetNumber. Then you fill up the FunctionDetails table with all data you need to send to the function. Then, you call the function passing in the SetNumber as an argument. The function can get all values from the FunctionDetails table. Then, after calling the function, delete the row from the Header table (and allow a cascing delete to remove the detail rows).Concurrency is no problem if you do it that way, also .... but the CSV is the easiest, for sure.- Jeff |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-07-07 : 11:17:34
|
| It's possible to declare a variable as a table. Can a table variable be used as an input parameter? Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-07 : 11:18:56
|
| Hmm. Moo. :( Thanks for the ideas. I may go with the table approach as the input would be drawn from something similar. How very pesky though that it cannot be done more easily.-------Moo. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-07-07 : 12:02:59
|
| Not much feedback on whether a table can be used as input or not.Hope you'll post your results later.Sam |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-07 : 13:03:58
|
| I'm pretty sure you CAN'T pass a table variable as input to a function or stored proc.When it doubt, try it out !These all return errors:create procedure test(@t table (i int, b char(1)))asselect * from @tGOcreate procedure test2(@t table)asselect * from @tGOcreate function test3(@t table)return intasreturn (select count(*) from @t)- Jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-07 : 13:16:48
|
| Key point:Remember, though, that you might not always need to pass sets of data. For example, if you are always determining that set of data based on date ranges, or some other factor (like customerID or EmployeeType or something ....) then just pass the necessary info to the stored proc or UDF so that the set can be derived from within that object.i.e., instead of1. built CSV string of data between date1 and date2, for customer X2. call stored proc passing in CSVit is better to:1. call stored proc passing in date1, date2, customer X- Jeff |
|
|
|
|
|
|
|
|
Passing a set to a function