Problem with Stored Procedure :(

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

General SQL Server Forums

New to SQL Server Programming

Problem with Stored Procedure :(

Author

Topic

yessi
Starting Member

2 Posts

Posted - 2006-03-27 : 23:37:43

Hi guys.......i ahve a trouble with stored procedure

this is my table

EmployeeID ManagerID Amount
allen ryan 20000
andrew Richard 10000
bob sarah 1000
charles robert 1000
fred john 1000
james allen 10000
janet andrew 5000
jeff sarah 1000
john tricia 5000
mary john 1000
michelle richard 10000
peter robert 1000
richard ryan 20000
robert james 5000
ryan ryan above 20000
sarah michelle 5000
tricia allen 10000
veronica andrew 5000

For example if bob request price of purchase order less than 1000 the approval value will be set to bob
(because he has maximum amount=1000), but if price of purchase order more than 1000 and less than 5000 it will set the approval value will be set to Sarah(Because Sarah is manager of bob) and if the value
more than 5000 and less than 10000 it will set the approval value to michelle(cause michelle is sarah's manager) and if the value more than 10000 and less than 20000, the approval value will be set to Richard(cause richard is Michelle's Manager) and if value more than 20000, the approval value will be set to Ryan(Cause Ryan is a high level of approval and richard's manager).

I have created the stored procedure but i dont know to continue my stored procedure, cause i juts have a little understanding of next approval, I have craeted may stored procedure but with the simple one(:( )

CREATE PROCEDURE [dbo].[R_Next_Approval]

(
@vUserID as nvarchar(50),
@iAmount as int
)
AS

BEGIN
DECLARE @Manager as nvarchar(50)
DECLARE @Authorised as varchar(1)
DECLARE @temp as int
DECLARE @amount as int
SET @Manager = @vUserID
SET @temp=@iAmount
select amount, employeeID from Employees where amount =@iAmount

IF ( SELECT amount
FROM dbo.Employees
WHERE employeeID = @vUserID) =@iAmount
BEGIN
SET @Authorised = 'Y'
END
ELSE
SET @Authorised = 'N'

if ( SELECT amount
FROM dbo.Employees
WHERE employeeID = @vUserID) >= @temp
BEGIN
SET @Manager = ( SELECT
employeeID
FROM
dbo.Employees
WHERE
amount = @temp)
IF (SELECT amount FROM dbo.Employees WHERE employeeID = @Manager) = @iAmount
BEGIN
SET @Authorised = 'Y'
END

END

END
GO

Please help me........................what should i do in my stored procedure or did i miss something?

Best regards

Yessi

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 05:30:10

Something like

declare @Authorised nvarchar(50) ,@next nvarchar(50), @userid nvarchar(50)

select @next = @vUserID
while @Authorised is null and coalesce(@userid, @next) <> @next
begin
select @Authorised = case when Amount >= @iAmount then @next else null end ,
@userid = @next ,
@next = ManagerID
from tbl
where EmployeeID = @next
end

select AuthorisedBy = case when @userid = @next then 'high level manager ' = @userid else @userid end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-28 : 06:50:04

Hi all,

Yessi - this might be a little easier to get to grips with. Just run it...

--data
declare @t table (EmployeeID varchar(20), ManagerID varchar(20), Amount int)
insert @t
          select 'allen', 'ryan', 20000
union all select 'andrew', 'Richard', 10000
union all select 'bob', 'sarah', 1000
union all select 'charles', 'robert', 1000
union all select 'fred', 'john', 1000
union all select 'james', 'allen', 10000
union all select 'janet', 'andrew', 5000
union all select 'jeff', 'sarah', 1000
union all select 'john', 'tricia', 5000
union all select 'mary', 'john', 1000
union all select 'michelle', 'richard', 10000
union all select 'peter', 'robert', 1000
union all select 'richard', 'ryan', 20000
union all select 'robert', 'james', 5000
union all select 'ryan', 'ryan', null
union all select 'sarah', 'michelle', 5000
union all select 'tricia', 'allen', 10000
union all select 'veronica', 'andrew', 5000

--inputs
declare @vUserID nvarchar(50)
declare @iAmount int

set @vUserID = 'bob'
set @iAmount = 20001

--calculation
while 0 = 0
begin
    select @vUserID = ManagerID from @t where EmployeeID = @vUserID and Amount < @iAmount
    if @@rowcount = 0 break
end

select @vUserID

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources