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 |
|
brubaker
Starting Member
15 Posts |
 Posted - 2001-12-11 : 20:12:41
|
| I'm trying to follow everyone's advice by putting all of the logic of a site in stored procedures and user defined functions. However, I'm having a problem that I'm sure someone here can help me with.I've created about 13 user defined functions of which about 3 return tables as their output. The other 10 return INTs or DECs.When I call these functions from Stored Procedures, the 10 that return INTs or DECs all work, but the 3 that return tables do not. I get an 'invalid object name' error. Now, if I execute the function directly (and not from the stored procedure) inside the query analyzer, the functions that return tables work exactly as designed.I will say that the permissions seem to be set correctly, but I noticed an interesting difference between the two types of functions. If the procedure returns a single value, the only permissions you can set are for EXEC and DRI (what is DRI????). However, the functions that return tables have SELECT, INSERT, UPDATE, DELETE & DRI, but no EXEC, as permission options. In my case, since I didn't want permissions to be an issue (just for testing), I have them all selected.So in conclusion, does anyone know what is happening or did you have a similar problem? How can I call these functions from inside of stored procedures?Thanks,Bru |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-11 : 20:33:56
|
| DRI is Declarative Referential Integrity and refers to the REFERENCES permission. It allows or denies a user to create or enforce any defined foreign keys between tables. EXEC permission only applies to procedures and functions.Anyway, while perusing user-defined functions in Books Online (for the first time, so I could be wrong) I found a part about BEGIN...END blocks, and that commands cannot have permanent effects outside of the function. Maybe there's a stray line in your function that violates this or another rule. Can you post your function code?Since you're calling a function from a sproc and having problems, it may also be a scope issue. And are you using any dynamic SQL? That could easily cause a scope conflict that may not happen in Query Analyzer. |
 |
|
|
brubaker
Starting Member
15 Posts |
Posted - 2001-12-11 : 20:42:01
|
| The function is as follows:CREATE FUNCTION f_returnTopMembersScoresAndRanks()RETURNS TABLE AS RETURN(SELECT TOP 10 score = dbo.f_computeMemberScore(ID), rank = dbo.f_computeMemberRank(ID), correctPicks = dbo.f_validMemberPickCount(ID)FROM tableMembersORDER BY rank)It basically calculates a score, rank, and valid picks for every member in the table.There is no dynamic SQL and nothing else really complex as my skill level won't allow for that :)The three functions that are called all work in Query Analyzer and from ASP pages. All this function really does is aggregate the results.I hopes this helps you help me!Bru |
|
|
|
brubaker
Starting Member
15 Posts |
Posted - 2001-12-11 : 21:16:43
|
| Another interesting thing that I just discovered: if I take that code and put it in the stored procedure, it works fine. It only doesn't work in a function that returns a table.Bru |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-11 : 21:29:36
|
| Have you tried wrapping it into a BEGIN...END block as if it were a multi-statement table return? I don't think that's the problem either, but it might work. |
|
|
|
|
|
|
|
|
User Defined Functions that return tables