/* This set of test codes show creating and populating tables, 
creating procedures, and designing a called procedure together with 
the calling procedure for transferring data values using the 
output option. Copy this whole document and paste into your Query Analyzer. */

/* If you mess up the whole thing, just go back and start from the top. */

/* Begin by dropping all objects we created. Highlight all drop commands and 
execute them together. Don't worry about error messages. */

drop procedure prShowNetSalary

drop procedure prGetOneSalary

drop procedure prShowOneSalary

drop procedure prShowAllSalaries

drop table OutputTest

/* First, we create an example table. Highlight and execute the next command. */

create table OutputTest ( cEmpId char (3) primary key, cName char (30), mSalary money, 
                          mDeductions money )

/* Highlight and execute the following insert commands together. */

insert OutputTest values ( '003', 'Oying Castro', 2000, 500 )
insert OutputTest values ( '005', 'Wilhelm Lim', 3000, 700 )
insert OutputTest values ( '007', 'Mika Escala', 1000, 200  )

/* Test if table was  populated. Highlight and execute the command below. */

select * from OutputTest

/* Let's create a procedure to display details of all employees. Highlight and 
execute the following create procedure command. */

create procedure prShowAllSalaries as  
       print "Employee Salaries"
       print ""            
       select "Employee Id" = cEmpId, "Employee Name" = cName, 
              "Salary" = mSalary, "Deductions" = mDeductions
       from OutputTest 

/* Test the procedure by highlighting and executing the next exec command. */

exec prShowAllSalaries

/* Next, create a procedure to show details for one employee. Note carefully 
that we specify our @cEmpId variable for output since we plan to 
print it in our report title. However, we can still use it for input. 
Highlight and execute the next procedure. */

create procedure prShowOneSalary @cEmpId char (3) output as                              
begin
       if exists ( select * from OutputTest 
                  where cEmpId = @cEmpId )
       begin
             print "Details for Employee " + @cEmpId
             print ""
             select "Employee Name" = cName, 
             "Salary" = mSalary, "Deductions" = mDeductions
             from OutputTest where cEmpId =@cEmpId
       end
       else
             print "No employee with given ID"
end

/* Let's test the procedure using valid data. Highlight and 
execute the next command. */

exec prShowOneSalary '003'

/* Let's test the procedure with invalid data. Highlight and execute 
the next command. */

exec prShowOneSalary '001'

/* Now, let's create a procedure that will get and store the name, salary 
and deductions values from a particular row in the OutputTest table. Here, 
we specify in the create procedure line the output variables that will 
contain the values that will be returned to the calling procedure. There is 
no need to specify @cEmpId as for output since we do not plan to use it 
for display. The other variables like name, salary and deductions must be 
specified as for output since we plan to return their values to a calling 
procedure. Highlight and execute the following create procedure command. */

create procedure prGetOneSalary @cEmpId char (3),
                                @CName char (30) output,
                                @mSalary money output,
                                @mDeductions money output as                              
begin
       if exists ( select * from OutputTest 
                  where cEmpId = @cEmpId )
       begin
             select @cName = cName, @mSalary = mSalary, 
                    @mDeductions = mDeductions 
             from OutputTest where cEmpId = @cEmpId
             return 0
       end
       else
       begin
             return 1
       end
end

/* Note carefully that the above procedure is not meant to be executed 
by itself since it does not do anything but save values in output variable. 
Executing it with just the employee ID supplied will result in an error since SQL 
will be looking for values of the other three required parameters ( name, salary and 
deductions ). Let's try executing it with only the Employee ID supplied. Highlight 
the following exec command and execute it. */

exec prGetOneSalary '003'

/* Now let's try to execute the procedure and supply it with the required values. 
Highlight and execute the next exec command. */

exec prGetOneSalary '003', 'Oying Castro', 1000, 500

/* Did the procedure executed without error? Why? */

/* Now, let's create another procedure that will call the previous procedure 
and receive the salary values so we can display both the salary and 
deductions, then compute and display the net salary as well. We need to declare 
the variables that we will need before they are used in the exec command. 
Then, we specify the variables again with the output qualifier at the exec 
command itself. If the called procedure executed successfully, we save the 
returned values in our local variables and use them for displaying the values. 
Otherwise, we inform the user that the employee ID supplied was invalid. 
Note that we specify @cEmpId as for output again since we plan to display it 
in our report title. Highlight and execute the next create procedure command. */

create procedure prShowNetSalary @cEmpId char (3) output as
begin
       declare @cName char (30)
       declare @mSalary money
       declare @mDeductions money
       declare @iReturnValue int
       exec @iReturnValue = prGetOneSalary @cEmpId, 
                                           @cName output,
                                           @mSalary output, 
                                           @mDeductions output
       if @iReturnValue = 0 
       begin
             print "Salary Details for Employee " + @cEmpId
             print ""
             select "Employee Name" = @cName, "Gross Salary" = @mSalary, 
                    "Deductions" = @mDeductions, 
                    "Net Salary" = @mSalary - @mDeductions
       end   
       else
             print "No employee found for ID " + @cEmpId         
end

/* Now, let's test our new procedure using valid values. Highlight and 
execute the next exec command. */

exec prShowNetSalary '003'

/* Now, let's test it using invalid data. Highlight and execute the 
next exec command. */

exec prShowNetSalary '001'

/* Always remember to identify as paramenters and specify as for output 
the required values in the called procedure. Also, don't forget to save 
the values from the tables in the called procedure. In the calling procedure, 
declare the local variables that will store the returned values from 
the called procedure. Identify the local variables again and specify as 
for output when executing the called procedure. */ 

/* Questions or problems? Please e-mail me at apcastro111@Yahoo.com. Thanks.*/