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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-03-09 : 08:48:34
|
| Fargus writes "SQL Server 2000 - All Editions, SP3-----------------------------------I have a table with a large piece of text that needs to be posted as several records to a desintation table. I don't want to use cursors and a stored procedure due to performance and basically that it is a clunky solution. I can't use DTS so the DataPump option is not available.I developed a UDF that returns a TABLE variable holding the results that I want which is great, extremely fast using a table variable. I want to be able to use the original table as a source of data for this function and return a new result table. The solution of CROSS JOIN with the function works wonderfully if a literal is passed to the function but as soon as I try to use a column name in the parameter SQL Server thinks it is a table hint and complains bitterly.This should be such a simple solution and provides a flexible solution that can be used by my collegues too thus cutting costs - Anyone got anyidea why this keeps coming up or a way to maybe turn off table hints usage?" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-09 : 09:07:29
|
| You cannot, and should not, use columns in your SELECT as parameters in a table-valued UDF. You should be putting your data into a table variable or temp table first, and then joining to that. Or, don't use a UDF at all. If you are breaking up a delimited column in a table into multiple rows, join to a Numbers table and use that to parse your column, w/o using any UDF -- that will be the most efficient method.If you can post more specific info regarding what you are trying to do, we can help out further.Rob Volk has a nice article on doing things like this here: http://www.sqlteam.com/item.asp?ItemID=2652 |
 |
|
|
|
|
|
Multi-Line Statement UDF's in Cross Join...