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 |
|
LLatinsky
Starting Member
38 Posts |
 Posted - 2004-07-14 : 17:51:10
|
| I need to join a regular database table on a table returned by a user - defined function, input parameter for which among the other ones is a number of records to return. When I use SET ROWCOUNT @NumberOfRowsInsert into @table select columns ... within the functionI dont get any syntax errors, but when I am trying to run the script to create the function I am getting an error "Invalid use of 'UNKNOWN TOKEN' within a function."The moment I take the SET ROWCOUNT line out, the create function statement runs. I did not see any restrictions on using SET ROWCOUNT in user-defined functions. I was wondering what does this error mean and how can I fix it. Thank you. |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-07-14 : 18:32:00
|
| I would guess that it isnt deterministicTry using Top instead .... bum, but then you cant have a variable number of rows, because you cant use Exec.You could try the trick of generating a sequence number and only returning the rows that have the sequence number less than or equal to the count you want.Post your table and query and Ill have a think about it -- indeed if you are Inserting into your return table you could set up and Identity column and rely on that |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 21:05:43
|
| Can you put the SET ROWCOUNT @NumberOfRows in the thing that calls the UDF?Kristen |
|
|
|
LLatinsky
Starting Member
38 Posts |
Posted - 2004-07-15 : 09:09:24
|
| No, unfortunately, I can't as it might affect the processing of other statements that run before the insert...select.Thank you. Lana |
|
|
|
|
|
|
SET ROWCOUNT in a UDF