I am calling a Sybase stored procedure X from another stored procedure Y. Following the answer to a similar question , I create a #tmp_table to hold the results from stored procedure X.
create table #tmp_table(
col1 int,
col2 varchar(100),
...
) exec sp_stored_procedureX 888, 'Parameter2', ...
select * from #tmp_table
The above successfully loads stored procedure X's results into #tmp_table but it shows the results of stored procedure X twice. I guess the first one is from "exec sp_stored_procedureX ..." part and the second one is from "select * from #tmp_table" which I intended. I don't want to display the results from the first "exec sp_stored_procedureX ..." part. How can I store data to #tmp_table without displaying it?
Please kindly let me know if more clarification/information is needed.
Thanks & Regards,
Kyoto
your syntax is incorrect for normal table in ASE. But for ASE, there's a special table name RPC table can map the output of procedure to a table format output. Maybe that's what you are looking for...And that also can be called from remote ASE. Here's a sample --
use omni_rpc
go
create table rmtbl
(
aint int null,
bchr char(10) null,
cchr char(10) null
)
go
insert rmtbl values (11, "b_row1", "c_row1")
insert rmtbl values (22, "b_row2", "c_row2")
insert rmtbl values (33, "b_row3", "c_row3")
insert rmtbl values (44, "b_row4", "c_row4")
insert rmtbl values (55, "b_row5", "c_row6")
go
create proc procImm @Colnames varchar(100), @NameT varchar(20), @nameCol varchar
(20), @value char(2)
as
execute ('select ' + @Colnames + ' from ' + @NameT + ' where '
+ @nameCol + ' = ' + @value)
Here @NameT and @Colnames are command parameters, and @value is a search parameter based on the terms defined at the beginning of the paper.
In the local server:
use test
go
sp_addobjectdef myrpc_imm, "THIS.omni_rpc..procImm", "rpc"
go
(return status = 0)
create existing table myrpc_imm
(
NameT varchar(20),
nameCol varchar(20),
value varchar(10)
)
external procedure at "THIS.omni_rpc..procImm"
go
select * from myrpc_imm
where NameT = 'rmtbl' and nameCol = 'aint' and value = '33'
go
NameT nameCol value
-------------------- -------------------- ----------
(0 rows affected)
dbcc traceon(11225)
go
00:00000:00017:2004/04/01 12:18:47.03 server DBCC TRACEON 11225, SPID 17
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
select * from myrpc_imm
where NameT = 'rmtbl' and nameCol = 'aint' and value = '33'
go
NameT nameCol value
-------------------- -------------------- ----------
33 b_row3 c_row3
(1 row affected)