MS SQL Viewer - computed field calling a udf question

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS SQL Viewer - computed field calling a udf question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-22 : 08:40:44

Merav writes "I am trying desparately to build a user defined function on the Microsoft SQL Server 2000 viewer and when it is performed I get the following message:

[Microsoft][ODB SQL Server Driver][SQL Server] Maximum stored procedure, function, trigger, or view nesting level exceeded

Is there any problem using sql MAX() built in function? How can I resolve this issue?

The UDF code is:
CREATE FUNCTION TestFunc (@Syn int)
RETURNS int AS
BEGIN
IF @Syn IS NULL
RETURN 1
DECLARE @NewPersonID INT
SELECT @NewPersonID = MAX(PersonID)
FROM GO_Test
WHERE SynagogueID = @Syn
RETURN ISNULL(@NewPersonID + 1, 1)
END

I call the function from the 'Formula' propery of a computed field of a table like this:
([dbo].[TestFunc]([SynagogueID]))

while SynagogueID is a field on the same table.

When opening the table from the MS sql server I get the error mentioned above."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-22 : 10:03:04

Merav,

What do you mean by
-- Microsoft SQL Server 2000 viewer ?
-- opening the table from the MS sql server ?

Did u try calling ur function as : Select dbo.TestFunc([SynagogueID])
-- U can check whether Max does something (I don't think it will in this case) , by replacing that with a hardcoded valu for testing
-- Also wite a simple function and check whether the function is incorrect or the method of calling is incorrect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources