A very tough TSQL question, er... I mean CHALLENGE

[NuclearNed]

Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@nGadgetKey'

I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

SELECT TOP 10 *
FROM gadget, udf_gadget_values(gadget.gadget_key)

The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

Anything goes (i.e. if the function needs to be modified, then so be it). If anyone can give a good idea about how to do this, then they get a :beer: or two from me.

 

[WannaFly]

why are you doing select top 10 if you only need the first row? and why is there no where clause?

SELECT TOP 1 G.*, @nGadgetKey from gadget G where G.nGadgetKey=@nGadgetKey

^^ that should work, you might have to replace G.* with G.<fieldname> for all the fields you want to select.

 

[NuclearNed]

Originally posted by: WannaFly
why are you doing select top 10 if you only need the first row? and why is there no where clause?

SELECT TOP 1 G.*, @nGadgetKey from gadget G where G.nGadgetKey=@nGadgetKey

^^ that should work, you might have to replace G.* with G.<fieldname> for all the fields you want to select.

I want to select the top 10 records from 'gadget', and for each 'gadget' record there is an associated record that is in the table produced by the function. There is no WHERE clause because I don't care what the first 10 records from 'gadget' are, I just want the top 10. But if it helps, here is the same query, slightly modified:

SELECT *
FROM gadget,
udf_gadget_values(gadget.gadget_key)
WHERE gadget_key IN (1, 2, 3, 5, 76, 98)

The real issue is this: when SQL server selects a record from 'gadget', how can I pass 'gadget_key' to udf_gadget_values as an input parameter???

 

[torpid]

What does the function do? It seems to me that it should be a view that you work with in queries and not a function.

 

[mAdMaLuDaWg]

I'm not sure if this would work, but try it and lemme know:

Select * from gadget UNION udf_gadget_values(gadget_key)

Now this is assuming that the table structure of the table returned by the udf is exactly the same as the table structure of gadget.

BTW, are you doing this just for the heck of it or are you building a real app. If the latter, I think you'd want to reconsider your db design.

 

[NuclearNed]

Originally posted by: mAdMaLuDaWg
I'm not sure if this would work, but try it and lemme know:

Select * from gadget UNION udf_gadget_values(gadget_key)

Now this is assuming that the table structure of the table returned by the udf is exactly the same as the table structure of gadget.

BTW, are you doing this just for the heck of it or are you building a real app. If the latter, I think you'd want to reconsider your db design.

Thanks for the suggestion, but the table structures don't match.

This is a real app. I wrote a really complex user-defined function that does exactly what my users want, but now they want the ability to join to the table returned by the function.

 

[NuclearNed]

Originally posted by: torpid
What does the function do? It seems to me that it should be a view that you work with in queries and not a function.

The function calculates a whole bunch of values that are dependent on the gadget key that is passed to it. The problem with using a view is that the calculations are extremely complex and time consuming, so I want to perform them only on the current gadget of interest and not all gadgets simultaneously (thus the input parameter). There are over 350,000 gadgets in the gadget table, and performing this function on all of them would take hours (literally).

 

[torpid]

Originally posted by: NuclearNed

Originally posted by: torpid
What does the function do? It seems to me that it should be a view that you work with in queries and not a function.

The function calculates a whole bunch of values that are dependent on the gadget key that is passed to it. The problem with using a view is that the calculations are extremely complex and time consuming, so I want to perform them only on the current gadget of interest and not all gadgets simultaneously (thus the input parameter). There are over 350,000 gadgets in the gadget table, and performing this function on all of them would take hours (literally).

Edit: nevermind I misunderstood the issue.

Have you tried using a subquery in some manner?

 

[NuclearNed]

Originally posted by: torpid

Originally posted by: NuclearNed

Originally posted by: torpid
What does the function do? It seems to me that it should be a view that you work with in queries and not a function.

The function calculates a whole bunch of values that are dependent on the gadget key that is passed to it. The problem with using a view is that the calculations are extremely complex and time consuming, so I want to perform them only on the current gadget of interest and not all gadgets simultaneously (thus the input parameter). There are over 350,000 gadgets in the gadget table, and performing this function on all of them would take hours (literally).

Edit: nevermind I misunderstood the issue.

Have you tried using a subquery in some manner?

Yes, but nothing has worked yet.

 

[mAdMaLuDaWg]

Originally posted by: NuclearNed

Originally posted by: mAdMaLuDaWg
I'm not sure if this would work, but try it and lemme know:

Select * from gadget UNION udf_gadget_values(gadget_key)

Now this is assuming that the table structure of the table returned by the udf is exactly the same as the table structure of gadget.

BTW, are you doing this just for the heck of it or are you building a real app. If the latter, I think you'd want to reconsider your db design.

Thanks for the suggestion, but the table structures don't match.

This is a real app. I wrote a really complex user-defined function that does exactly what my users want, but now they want the ability to join to the table returned by the function.

Could you consider putting some of that logic in the application logic?

After re-reading your problem, I came up with this... again, I'm not sure if it would work but anyways...

Select * from gadget INNER JOIN udf_gadget_values(gadget_key) AS Temp ON Temp.Gadget_key=gadget.Gadget_key

If you could list the code of the udf, I could possibly help you more.

 

[DT4K]

Create a temp table with the structure of the final resultset you want.
Create a cursor for your "select top 10 * from gadget"
For each cursor row, execute the udf and throw the results into the temp table
Then select * from the temp table

 

[mAdMaLuDaWg]

BUT CURSORS ARE EEEEVIL ....

Seriously though, there is a reason why every single service pack for SQL patched some issue or the other related to cursors. I'd stay away from them if I were you. I've learnt that if you get to the point of using cursors, your db design is pretty bad or you are doing stuff you are supposed to be doing in your application logic.

 

[torpid]

Originally posted by: mAdMaLuDaWg
BUT CURSORS ARE EEEEVIL ....

Seriously though, there is a reason why every single service pack for SQL patched some issue or the other related to cursors. I'd stay away from them if I were you. I've learnt that if you get to the point of using cursors, your db design is pretty bad or you are doing stuff you are supposed to be doing in your application logic.

Cursors are fine, just not in sql server. Other DBMS do them better... somewhat. They are still evil, of course.

This doesn't work?

Select top 10 * from gadget g inner join (select udf_gadget_values(g)) gv on ...

 

[DT4K]

Yeah. Temp tables are EVIL too.
And don't even get me started on sp_OA procs.

But sometimes cursors and temp tables make really nasty tasks simple and easy.
And somtimes they are a better, faster, and easier choice than completely redesigning your db. Personally, I've used cursors and temp tables quite a few times and never had any issues with either.

90% of our business logic is in sp's, so yeah, we're probably doing lots of stuff that should really be done in a real programming language instead of T-SQL. Too late now though, LOL.

I'm not real familiar with udf's, but yeah, if you can do it with a simple join, that's definitely better.

 

[mAdMaLuDaWg]

Originally posted by: torpid

Originally posted by: mAdMaLuDaWg
BUT CURSORS ARE EEEEVIL ....

Seriously though, there is a reason why every single service pack for SQL patched some issue or the other related to cursors. I'd stay away from them if I were you. I've learnt that if you get to the point of using cursors, your db design is pretty bad or you are doing stuff you are supposed to be doing in your application logic.

Cursors are fine, just not in sql server. Other DBMS do them better... somewhat. They are still evil, of course.

This doesn't work?

Select top 10 * from gadget g inner join (select udf_gadget_values(g)) gv on ...

Ding ding ding... winn4r !

 

[mAdMaLuDaWg]

Originally posted by: DT4K
Yeah. Temp tables are EVIL too.
And don't even get me started on sp_OA procs.

But sometimes cursors and temp tables make really nasty tasks simple and easy.
And somtimes they are a better, faster, and easier choice than completely redesigning your db. Personally, I've used cursors and temp tables quite a few times and never had any issues with either.

90% of our business logic is in sp's, so yeah, we're probably doing lots of stuff that should really be done in a real programming language instead of T-SQL. Too late now though, LOL.

SPs are fine. They should be used as much as possible. However, MS hasn't implemented cursors well in SQL Server...

that being said, you also need to draw a line between your application and db logic so you don't overwhelm either one.

 

[DT4K]

Hey, just wait until we are able to write sp's in .Net languages. Cursors and temp tables are nothing compared to the way people will abuse this new ability.:evil:

 

[DT4K]

Originally posted by: mAdMaLuDaWg
SPs are fine. They should be used as much as possible. However, MS hasn't implemented cursors well in SQL Server...

I said sp_OA's, not sp's. COM automation in SQL procs = bad idea.

 

[mAdMaLuDaWg]

Originally posted by: DT4K

Originally posted by: mAdMaLuDaWg
SPs are fine. They should be used as much as possible. However, MS hasn't implemented cursors well in SQL Server...

I said sp_OA's, not sp's. COM automation in SQL procs = bad idea.

Yeah, you also said you did 90% of your business processes in sps.

I honestly have never used COM Automation in SQL Procs... I could only imagine the havoc you could wreak with that stuff :evil:

 

[DT4K]

Originally posted by: mAdMaLuDaWg
I honestly have never used COM Automation in SQL Procs... I could only imagine the havoc you could wreak with that stuff :evil:

Actually, it's worked ok for us so far. We did it because we wanted to be able to execute SAP transactions from an sp and that was the easiest way. But you can have issues if you create and destroy objects too rapidly. It's just one of those things that sounded like a cool idea at the time, then the more you think about it, the more it just doesn't sound smart.

 

[oog]

what was wrong with the suggestion to just join gadget with the table that comes out of the udf?

 

[NuclearNed]

Originally posted by: oog
what was wrong with the suggestion to just join gadget with the table that comes out of the udf?

The function requires a parameter, which happens to be gadget_key. The crux of the problem is that there is no way to pass gadget_key from the gadget table to the function during execution of the SQL statement.

 

[oog]

can you rewrite the function to not be gadget_key specific? it sounds like you're going to join on this column anyway, so you can remove it as a parameter from the function, let it create the full table as the result and then join it to reduce the resultset.

 

[torpid]

OK, further research reveals that you can't pass a column identifier to a table-valued function. Seems like your best option would be to use a stored proc that works with a temp table.

 

[oog]

Originally posted by: torpid
OK, further research reveals that you can't pass a column identifier to a table-valued function. Seems like your best option would be to use a stored proc that works with a temp table.

it's often helpful to use a table variable instead of a temp table if this is the approach you're taking.