One of the basic requirements while developing in Sybase ASE or any other database is to get the resultset from a stored procedure into another stored procedure or a batch file. While it is pretty simple in Microsoft SQL Server 2005 and later but its much difficult and ofcourse a grey area in Sybase ASE. In this first method we will try to show you the indirect way of getting the result set from one stored procedure back into some other stored procedure. Now, coming back to work..
To get the resultset from one stored procedure say - proc1 into proc2 we will create a temporary table in proc1 with the same structure as the resultset that we expect from proc2 and then we will insert resultset in this temporary table already created in proc1. This will help us to get the resultset back from the proc2 procedure.
To demonstrate this we will first create a stored procedure proc2 which will give us the results from one of our table Employee. The procedure is as follows -
create procedure proc2
as
begin
select
EmployeeId,
EmployeeName,
EmployeeSalary
from
Employee
where
EmployeeName = 'TOM'
end
go
The Employee table is is defined as follows -
create table Employee
(
EmployeeId int,
EmployeeName varchar(100),
EmployeeAge int,
EmployeeSalary float
)
go
Now as we can see that the proc2 stored procedure currently selects a certain subset of the table Employee where the EmployeeName = 'TOM' but the problem here is that we wont be able to use this resultset directly within another stored procedure or a batch script. So what should we do now?
To get this result back into another stored procedure or a batch script we will need to modify this existing stored procedure proc2. In this approach we will be modifying the existing stored procedure which is ofcourse not what we might need in all scenario's. But as of now we will follow this approach and introduce a new temporary table #ResultSetTable to store the result from the procedure proc2. The structure of this temporary table #ResultSetTable will be as follows -
create table #ResultSetTable
(
EmployeeId int,
EmployeeName varchar(100),
EmployeeSalary float
)
go
As you can see the structure of this table is similar to the resultset that we want from proc2 stored procedure. Now in the next step we need to modify the existing stored procedure proc2 but as temporary table #ResultSetTable will be required in some other batch or stored procedure hence we cannot create #ResultSetTable in proc2 procedure. So the modifications we have to do are as follows -
create procedure proc2
as
begin
insert into #ResultSetTable
select
EmployeeId,
EmployeeName,
EmployeeSalary
from
Employee
where
EmployeeName = 'TOM'
end
go
As we can see that the above procedure will now insert resultset into the table #ResultSetTable instead of simply selecting the resultset. But as we can also see that we cannot make this modification simply as ASE server will prompt us that the temporary table #ResultSetTable does not exist in system catalog. So before the stored procedure proc2 gets created we will create the temporary table #ResultSetTable using the create script and then execute create script for the proc2 procedure in the same session.
Once proc2 has been created we can use the resultset now stored in temporary table #ResultSetTable in our calling stored procedure proc1 or any batch script by simply calling the procedure proc2 and working on the result in the table #ResultSetTable.
An example of the calling stored procedure is as follows -
create procedure proc1
as
begin
create table #ResultSetTable
(
EmployeeId int,
EmployeeName varchar(100),
EmployeeSalary float
)
exec proc2
select
EmployeeName,
EmployeeSalary
from
#ResultSetTable
end
go
As we can see here that before calling the stored procedure proc2 we first created the temporary table #ResultSetTable as it is a session specific table and needs to exist before proc2 gets called.
So we are done with our task however the only problem with this approach is that we had to modify proc2 stored procedure which is not always desirable. In our second method I will show you how to achieve the same result without modifying the existing stored procedure, but all that in the other article.
Sybase is riddled with problems as it is...why would you add a "hidden" temp table to scenario?
ReplyDelete