Nested Stored Procedure - SQL Server Forums

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)

Nested Stored Procedure

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-02-09 : 01:50:23

Hi,

I have a stored procedure (proc_CheckUserIsAdmin) that checks if a user has the website admin privilege. This stored procedure takes the user ID as parameter and returns a 1 if this user has the privilege, and returns a 0 if otherwise.

There's another stored procedure that returns the entries in the Order table. Now I'd like to nest that first stored procedure into the second one, so that if the user has admin privilege, he can see all order entries. Otherwise he can only see his own orders. What I have is:

CREATE PROCEDURE stp_Bro_GetCategoryTree
@UserID int
AS
  DECLARE @Result integer
   EXEC @Result = proc_CheckUserIsAdmin @UserID
   IF (@Result = 1)
    BEGIN
     SELECT * FROM order
     RETURN
    END
   ELSE
    BEGIN
     SELECT * FROM order WHERE userID=@UserID
     RETURN
    END
  GO

But it only returns order of userID=@UserID even if the user has site admin privilege. What is wrong?

ywb.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 02:02:15

execute
DECLARE @Result integer
EXEC @Result = proc_CheckUserIsAdmin 'UserId passed'
and print the value of @Result.

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-02-09 : 02:35:00

That prints correctly - 1 for users with site admin privilege and 0 for those without

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 03:07:31

If the value is correct, then it should work.
Also try this

If exists(select * from AdminTable where userID=@UserID
and admin='true')
SELECT * FROM order
else
SELECT * FROM order WHERE userID=@UserID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources