Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2006
    Posts
    39

    Unanswered: creating transact procedures

    i need to create procedures with if statements
    i am confused since i did not learn much about it

    i need to do the following
    a) If the employee is not a manager the procedure should print “Name is Individual Contributor”
    b) If the employee is a manager, the procedure should list all the employees that report to that manager.
    c) If the supplied EmployeeID does not exist, the procedure should print “Employee with ID NNN does not exist.”

    This is the table
    CREATE TABLE Employee
    (EmployeeID int NOT NULL
    CONSTRAINT Employee_EmployeeID_PK PRIMARY KEY
    , Name varchar(50) NOT NULL
    , SIN char(9) NOT NULL
    , LoginID varchar(256) NOT NULL
    , ManagerID int NULL
    , BirthDate smalldatetime NOT NULL
    , MaritalStatus char(1) NOT NULL
    , Gender char(1) NOT NULL
    , HireDate datetime NOT NULL
    , VacationHours smallint NOT NULL
    CONSTRAINT Employee_VacationHours_DF DEFAULT ((0))
    , SickLeaveHours smallint NOT NULL
    CONSTRAINT Employee_SickLeaveHours_DF DEFAULT ((0))
    , IsManager bit
    , NumReports int
    , ModifiedDate datetime NOT NULL
    CONSTRAINT Employee_ModifiedDate_DF DEFAULT (getdate())

    I am thinking of using IsManager as TRUE or FALSE
    but i dont know how to put it together

    this is my sad try for now
    CREATE PROCEDURE ListOrganizationMC
    @employeeID int,
    @managerID int,
    @isManager bit
    AS
    select name
    from employeemc

    if @ismanager = 0
    Begin
    Print 'Name is Individual Contributor'
    end
    SET @employeeID = @managerID;
    go

    im sure the top part of the parameters are wrong
    i just want to enter one parameter

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20080322  NZDF solution for http://www.dbforums.com/showthread.php?t=1628548
    
    CREATE PROCEDURE t1628548
       @piEmployee		AS INT
    AS
    
    DECLARE
       @iEmployee		INT
    ,  @iFetch		INT
    ,  @cName		NVARCHAR(50)
    
    SET @iFetch = -1
    
    DECLARE zt1628548 CURSOR FOR SELECT
       EmployeeID, Name
       FROM Employee
       WHERE  (@piEmployee) = ManagerID
    
    OPEN zt1628548
    
    FETCH zt1628548 INTO @iEmployee, @cName
    
    WHILE 0 = @@fetch_status
       BEGIN
          SET @iFetch = 1 + @iFetch
          FETCH zt1628548 INTO @iEmployee, @cName
       END
    
    IF 2 != @@fetch_status
       PRINT 'Cursor fetch error in dbo.t1628548, see previous output error(s).'
    
    CLOSE zt1628548
    
    DEALLOCATE zt1628548
    
    IF @iFetch < 0
       BEGIN
          SELECT *
             FROM Employee
             WHERE  (@piEmployee) = @piEmployee
    
          IF 0 = @@rowcount
             BEGIN
                PRINT '“Employee with ID NNN does not exist.”'
                RETURN
             END
          ELSE PRINT '“Name is Individual Contributor”'
       END
    
    RETURN
    GO
    -PatP

  3. #3
    Join Date
    Oct 2006
    Posts
    39
    Pat Phelan, you're amazing
    the codes seem very advanced for me to understand
    like these i don't understand:

    , @iFetch INT
    , @cName NVARCHAR(50)
    what is iFetch and why is there an "i" in front
    so is "c" in front of name. i dont know what those prefixes mean.


    SET @iFetch = -1
    dont know what this does, something to do with the bits of boolean

    what does a cursor do?


    if i dont know ifetch, then i dont understand this whole line
    FETCH zt1628548 INTO @iEmployee, @cName

    WHILE 0 = @@fetch_status
    BEGIN
    SET @iFetch = 1 + @iFetch
    FETCH zt1628548 INTO @iEmployee, @cName
    END

    IF 2 != @@fetch_status
    PRINT 'Cursor fetch error in dbo.t1628548, see previous output error(s).'

    CLOSE zt1628548

    DEALLOCATE zt1628548


    Thanks for helping
    i am very thankful for such a expert helping
    so i shall call u a teacher or a master

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, i agree, that was a very tasty expert helping

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before we dive into the code I posted and how it works, let's be sure that we get the correct code first. That code was written to meet the technical requirements in your original post, but I strongly suspect that there are better ways to solve the real underlying problem or problems.

    First order of business, what real world problem are you trying to solve? What business, personal, or other need are you trying to meet? Once we determine what need or needs you've actually got, we can focus on meeting those needs without having to worry about predetermined technical details that don't help solve those problems.

    Second, we need to consider how you want to use this solution. Since you are looking for a stored procedure, I have to assume that you are running ASP or .NET code of some kind. If you are using ColdFusion or PHP, very different choices are available and would make it easier for you to produce a much better final result. Understanding where this part of the solution fits into your overall solution will help me to craft a better answer for you.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, the real world situation is a homework assignment

    just look at his last three threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by Pat Phelan
    Before we dive into the code I posted and how it works, let's be sure that we get the correct code first. That code was written to meet the technical requirements in your original post, but I strongly suspect that there are better ways to solve the real underlying problem or problems.

    First order of business, what real world problem are you trying to solve? What business, personal, or other need are you trying to meet? Once we determine what need or needs you've actually got, we can focus on meeting those needs without having to worry about predetermined technical details that don't help solve those problems.

    Second, we need to consider how you want to use this solution. Since you are looking for a stored procedure, I have to assume that you are running ASP or .NET code of some kind. If you are using ColdFusion or PHP, very different choices are available and would make it easier for you to produce a much better final result. Understanding where this part of the solution fits into your overall solution will help me to craft a better answer for you.

    -PatP
    R937 is right, he had helped me few times
    XD
    i am doing a school assignment
    i am using SQL Server Management Studio

    there's like 5 parts to this assignment, but only this part and another question dealing with triggers is hard to understand since the prof didn't teach us much and expect us to know how to write these.

    the prof only talked about a few if statements
    and the general.
    so that's why i dont understand the "fetch" ,etc.
    Last edited by Chanyeehon; 03-22-08 at 17:58.

  8. #8
    Join Date
    Oct 2006
    Posts
    39
    i just tried the code
    it allows the code to run successfully
    but it doesn't run the way the question wants it to be:

    i have 5 rows,
    2 managers and 3 employees
    so employeeID has 5 rows, 1-5

    when i run it:
    execute t1628548 N'1' or 1-5
    it just gives me all the rows, which is the same as (select * from employee)
    when i want run employeeID 1 , it should run
    print “Name is Individual Contributor”
    because he is not the manager

    AND
    when i run printexecute t1628548 anything higher than 5
    it still gives me all the rows
    it should print “Employee with ID NNN does not exist.”'

    so
    If the employee is a manager, the procedure should list all the employees that report to that manager, but it only gives me
    all the rows
    Last edited by Chanyeehon; 03-22-08 at 18:11.

  9. #9
    Join Date
    Oct 2006
    Posts
    39
    i believe there should be a more simple way of doing this

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are certainly simpler methods, but I tried to meet the tech spec that you posted rather than code simply.

    My bad on that, you need to change line 40 to read
    Code:
             WHERE  (@piEmployee) = EmployeeID
    -PatP

  11. #11
    Join Date
    Oct 2006
    Posts
    39
    it still doesn't work properly

    from each employee, they just show a whole row
    and when i input a manager, it gives me the
    'Cursor fetch error in dbo.t1628548, see previous output error(s).''

  12. #12
    Join Date
    Oct 2006
    Posts
    39
    this is the whole question

    2. Create a stored procedure ListOrganization+YourInitial that will for a given EmployeeID do the following:
    a) If the employee is not a manager the procedure should print “Name is Individual Contributor”
    b) If the employee is a manager, the procedure should list all the employees that report to that manager.
    c) If the supplied EmployeeID does not exist, the procedure should print “Employee with ID NNN does not exist.”
    In order to test the procedure, insert five rows in the table: two for managers, and three for people reporting to them. Run the procedure for a manager, for the employee and for a non-existing employee ID.



    i tried some, but i didnt save it
    sorry
    i just simply tried using Ismanager as an if statement

    PHP Code:
    EmployeeID  Name                                               SIN       LoginID                                                                                                                                                                                                                                                          ManagerID   BirthDate               MaritalStatus Gender HireDate                VacationHours SickLeaveHours IsManager NumReports  ModifiedDate
    ----------- -------------------------------------------------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------------------- ------------- ------ ----------------------- ------------- -------------- --------- ----------- -----------------------
    5           Mackenzie                                          233233233 asdas                                                                                                                                                                                                                                                            0           1944-11-21 00:00:00     s             m      2222-03-22 00:00:00.000 0             0              1         NULL        2009-03-03 00:00:00.000
    1           pain                                               213423412 painfromhell                                                                                                                                                                                                                                                     5           1985
    -11-22 00:00:00     s             m      2008-03-21 22:50:18.950 0             0              0         NULL        2008-03-21 22:50:18.950
    2           oddball                                            213453412 cheese                                                                                                                                                                                                                                                           5           1986
    -11-22 00:00:00     s             m      2008-03-21 22:50:18.950 0             0              0         NULL        2008-03-21 22:50:18.950
    3           joy joyce                                          213453412 totheworld                                                                                                                                                                                                                                                       4           1986
    -11-22 00:00:00     s             m      2008-03-21 22:54:35.983 0             0              0         NULL        2008-03-21 22:54:35.983
    4           jack russel                                        213423312 jacky23                                                                                                                                                                                                                                                          0           1985
    -11-22 00:00:00     s             m      2008-03-21 22:54:35.983 0             0              1         NULL        2008-03-21 22:54:35.983

    (5 row(saffected

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •