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 |
|
groston
Yak Posting Veteran
61 Posts |
 Posted - 2005-12-05 : 16:33:03
|
| In my DB, I have a the following tables:CREATE TABLE myTable (col1 int, col2 int, col3 float, col4 datetime)CREATE TABLE otherTable (col1 int, col2 int, val1 int, val2 int)I also have a Table Valued Function that does thisCREATE FUNCTION myFunc (col1 int, col2 int)RETURNS TABLE (val1 int, val2 int)What I would like to do is the following:SELECT myTable.*, myFunc (col1, col2)FROM myTable(Needless to say, the actual code is more involved, but I think, hope, that this captures its essense.)Anyway, when I try running this code, I get:Invalid object name: 'dbo.myFunc'Looking into this, it seems that the problem is that a TVF must be treated like an actual table, thus, sticking a table in the SELECT clause is not correct. So, since this is a table, stick it in the FROM clause. However, this causes the error:Incorrect syntax near 'col1'This also makes sense because the FROM clause cannot pass row values (my explaination is probably way off, but that this is wrong also makes sense).So, the question is this - what can I do to achieve the desired results. (I was able to make this work using three scalar valued functions, but this approach is really inefficient.)Thanks! |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-05 : 16:42:11
|
| If you are just trying to JOIN the data from tables1 and tables2 you can do that without the need for a User Defined Function at allselect t1.*, t2.val1, t2.val2from myTable t1 inner join otherTable on t2.col1 = t1.col1 and t2.col2 = t1.col2 |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-05 : 16:43:25
|
| I missed the alias to t2.. the query should be:select t1.*, t2.val1, t2.val2from myTable t1 inner join otherTable t2 on t2.col1 = t1.col1 and t2.col2 = t1.col2 |
|
|
|
groston
Yak Posting Veteran
61 Posts |
Posted - 2005-12-05 : 16:56:29
|
| You are quite correct. However, the UDF part will be reused in a number of stored procedures, so wrapping it up as a UDF has value. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-05 : 20:30:56
|
| So wrap up the whole query as is, and have it called/returned in the stored procedures rather than having it only return half and then joining to get the other half. Or simply make the query as is a view that you can then select anytime you want from Stored Procedures or elsewhere. Just a thought. |
|
|
|
groston
Yak Posting Veteran
61 Posts |
Posted - 2005-12-07 : 09:30:53
|
| druer,I had forgotten all about using views - I rarely use them. In thsi case, however, it seems to be a good solution. Thanks! |
|
|
|
|
|
|
|
|
Using a Table Valued Function in a query