0

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

Kyoto
  • 37
  • 5
  • Thanks for taking a look so promptly. Correct. I tried `insert into #tmp_table exec sp_stored_procedureX 888, 'Parameter2', ...` and `insert into #tmp_table (col1, col2, ...) exec sp_stored_procedureX 888, 'Parameter2', ...` but both returned "Incorrect syntax near the keyword exec" error. I was wondering if there is any workaround. – Kyoto Apr 14 '22 at 07:11
  • 1
    `create table ... exec proc` (as a single command) is invalid syntax in Sybase ASE so there's no way the output from procedure X's invocation is being stored in the #temp table ***unless*** a) procedure X has been coded to explicitly insert data into the temp table or b) this isn't Sybase ASE; what is your ASE version (`select @@version`)? – markp-fuso Apr 14 '22 at 14:26
  • *note:* `create table ... exec proc` *is* valid syntax when treated as 2 separate commands ... `create table ... ` followed by `exec proc` – markp-fuso Apr 14 '22 at 15:14
  • Thank you for taking a look. The result of the query `select @@version` is `Adaptive Server Enterprise/15.7/EBF 26397 SMP SP138 /P/x86_64/Enterprise Linux/ase157sp138x/4002/64-bit/FBO/Mon Aug 29 20:46:15 2016`. – Kyoto Apr 14 '22 at 23:18
  • thanks; just wanted to make sure you were really on ASE (some people mistakenly think ASE when they're really running SQLAnywhere or IQ); anyhoo, my previous comments steill apply re: ASE; regards – markp-fuso Apr 14 '22 at 23:22
  • @markp-fuso His syntax is incorrect for normal table-- But in ASE15.x/16.x I think he can use rpc table to map a procedure to a table like MDA tables... So maybe he is asking for how to implement RPC table in ASE? – EisenWang Apr 26 '22 at 02:05

1 Answers1

1

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)
EisenWang
  • 189
  • 1
  • 10