Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Unhappy Unanswered: Quick SQL Homework Help!

    Hi everyone,

    I'm new to SQL, and need help with the following problem:

    'Create a stored procedure, name employee salary, that will receive two parameters, the employee's id (eid) and the salary increase (incSal). The procedure will then increment the salary by the amount of incSal. The procedure will then print the employee id, name, address and the new salary.'

    I don't ask for answers to homework, but today, I'm desparate. I would have asked for help earlier but for my final exam and project for this class. This homework is due later on in the evening.
    PLEASE HELP

  2. #2
    Join Date
    Sep 2003
    Posts
    71

    Question

    any attempt from your side

  3. #3
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Unhappy This is as far as I got

    Originally posted by skd
    any attempt from your side

    CREATE PROC INCSAL
    STORED PROCEDURE
    @EMPLOYEE ID nvarchar(20),
    @INCREASE SALARY int
    AS
    BEGIN
    INSERT INTO SALARY (EMP_ ID, INC_SAL)
    VALUES (@EMPLOYEE ID,@INCREASE SALARY)
    PRINT "EMP_ ID", ;
    PRINT "EMP_ NAME" ;
    PRINT "ADDRESS";
    PRINT "NEW SALARY"



    END
    STORED PROCEDURE AS

  4. #4
    Join Date
    Sep 2003
    Posts
    71
    see if this works
    ---------------------
    CREATE OR REPLACE PROCEDURE name_employee_salary (
    p_Eid IN NUMBER,
    p_IncSal IN NUMBER ) AS

    v_Eid employee.employee_id%TYPE;
    v_Ename employee.employee_name%TYPE;
    v_Eaddress employee.employee_address%TYPE;
    v_Esalary employee.employee_salary%TYPE;

    BEGIN

    UPDATE employee
    SET salary=salary + p_IncSal
    WHERE employee_id = p_Eid
    COMMIT;


    SELECT employee_id, employee_name, employee_address, employee_salary
    INTO v_Eid, v_Ename, v_Eaddress, v_Esalary
    FROM employee
    WHERE employee_id=p_Eid;

    DBMS_OUTPUT.PUT_LINE ('employee Id : ' || TO_CHAR(v_Eid) );
    DBMS_OUTPUT.PUT_LINE ('employee Name : ' || v_Ename );
    DBMS_OUTPUT.PUT_LINE ('employee Address : ' || v_Eaddress );
    DBMS_OUTPUT.PUT_LINE ('employee New Salary: ' || TO_CHAR(v_Esalary) );

    END;

  5. #5
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Talking Thanks a lot!

    This is far better than what I had. I'm tweaking it in SQL now. Thanks a bunch.

  6. #6
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Talking

    My professor didn't show us this:

    DBMS_OUTPUT.PUT_LINE ('employee Id : ' || TO_CHAR(v_Eid) );
    DBMS_OUTPUT.PUT_LINE ('employee Name : ' || v_Ename );
    DBMS_OUTPUT.PUT_LINE ('employee Address : ' || v_Eaddress );
    DBMS_OUTPUT.PUT_LINE ('employee New Salary: ' || TO_CHAR

    Rather, he wants us to use the PRINT function:

    I think this is how it must look:

    PRINT ("Employee Name", Name)
    PRINT ("Employee Salary", Salary)
    PRINT ("Employee ID", EID)

    I don't even know if I have the code right, but thanks again.

  7. #7
    Join Date
    Sep 2003
    Posts
    71
    what sql you are using, i gave you pl/sql verison.

    PRINT looks ok to me.
    if doesn't works then try
    PRINT "Employee ID" + EID

    good luck

  8. #8
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Talking

    I'm using Microsoft SQL Server 2000, but the cd also includes IBM DB2, and MySQL

  9. #9
    Join Date
    Sep 2003
    Posts
    71
    i am not familiar with sql server but can take a look at errors you getting

  10. #10
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Talking More on the error message

    This is the code I have so far:

    CREATE PROCEDURE name_employee_salary (
    @p_Eid ncvarchar(20),
    @p_IncSal
    AS
    BEGIN

    UPDATE employee
    SET salary=salary + p_IncSal
    WHERE employee_id = p_Eid
    COMMIT;


    SELECT employee_id, employee_name, employee_salary
    FROM employee
    WHERE employee_id=p_Eid;

    PRINT Employee_name
    PRINT Employee_salary
    PRINT EID

    END

    Here's the error message.

    Error 156: Incorrect syntax near the keyword 'BEGIN'.
    The name 'Employee_name' is not permitted in this context. Only constants, expressions, or variables allowed here. Columns names are not permitted.

    The error said the same thing about Employee_salary, and EID
    (I won't waste your time writing out the whole thing.)

  11. #11
    Join Date
    Sep 2003
    Posts
    71
    1) it might not require BEGIN and/or END which aer reserve word used in pl/sql.

    2) replace employee_id, employee_name, employee_salary by
    actual field of your employee table.

  12. #12
    Join Date
    Sep 2003
    Posts
    71
    post your question to SQL/SEVER forum
    which is http://www.dbforums.com/f7/

    good luck

  13. #13
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Don't you need to close the parentesis after @p_IncSal?:

    CREATE PROCEDURE name_employee_salary (
    @p_Eid ncvarchar(20),
    @p_IncSal )
    ....


    PS: I neither have any clue about MS SQL...
    Good luck.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  14. #14
    Join Date
    Oct 2003
    Location
    Miami
    Posts
    9

    Smile

    Originally posted by LKBrwn_DBA
    Don't you need to close the parentesis after @p_IncSal?:

    CREATE PROCEDURE name_employee_salary (
    @p_Eid ncvarchar(20),
    @p_IncSal )
    ....


    PS: I neither have any clue about MS SQL...
    Good luck.
    Thanks a lot for your help. I got the work finished on time.

Posting Permissions

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