Using a UDF Table In a Stored Procedure

Question

Using a UDF Table In a Stored Procedure

DWaldock

SSC Rookie

Points: 41
More actions
June 30, 2004 at 7:23 am

#108254

I have a User Defined Function (rules_Test) which takes one argument (account number) and returns a Table of information. The UDF works perfectly.
I then have an SP which needs to SUM() a given column of the table returned by the UDF. The following works perfectly:
SELECT SUM(column)
FROM [dbo].[rules_Test](@acctNo)
GROUP BY acct_no
However, the problem arises when I need to SELECT multiple values of @acctNo using wrapper. By which I mean:
SELECT (SELECT SUM(column)
FROM [dbo].[rules_Test](acctNo)
GROUP BY Srn) AS total_column
FROM tblAccount
This returns the error:
'acctNo' is not a recognized OPTIMIZER LOCK HINTS option.
If this isn't going to work, how can I get the SUM(column) value for all of the acctNo values in tblAccount?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 1

If I understand this correctly, you could try using a cursor on tblAccount, saving acctNo to @acctNo and execute the first select you coded for every row in tblAccount. You could insert the data into a temp table, and select from it after the cursor completed.

Cursors are slow, so I wouldn't do this if tblAccount is a large table.

When in doubt - test, test, test!

Wayne

Brian Filppu Old Hand Points: 322 More actions · Answer 2

Let me make sure i'm getting this right.

If you currently try it with say an account number of 1, it works.

But the problem is, when you try passing in

1

2

3

it doesn't work?

How about this...

Pass in '1,2,3' and in your sql statement say

and accountNumber in 1,2,3

DWaldock SSC Rookie Points: 41 More actions · Answer 3

You said:

"Pass in '1,2,3' and in your sql statement say

and accountNumber in 1,2,3"

I'm not sure quite what you are trying to achieve, but what I'm after is a rowset consisting of all of the accounts in tblAccounts with the UDF taking it's argument from the main query (ie: tblAccount.acctNo).

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 4

DWaldock: The statement you are executing won't work because 'SELECT SUM(column) FROM [dbo].[rules_Test](acctNo) ' returns a resultset, and you are trying to put it in the place where a column name of computed value should be. You need to redesign your solution.

Have you tried a cursor on tblAccount and executing the SUM Select for each loop of the cursor?

Also, try putting the SUM in the UDF, and changing the UDF to return a single value (the SUMMED value) instead of the table.

When in doubt - test, test, test!

Wayne

Brian Filppu Old Hand Points: 322 More actions · Answer 5

Ahhh, I see what you are saying. I agree with Wayne, you really should put your sum in your UDf. The interesting part is the error he is getting.

'acctNo' is not a recognized OPTIMIZER LOCK HINTS option.

makes me think it is really a syntax error - it thinks he's trying to give it a query hint.

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 6

I agree with Wayne. Get your UDF to return the sum(), then your SQL is very easy:

SELECT [dbo].[rules_Test](acctNo) FROM tblAccount

Regards

Peter