CODEWITHSUNDEEP
sqlstored-proceduressybase
Ed
Ed

Reputation:

Querying results of a stored procedure

I have a stored procedure that returns a large number of results, and would like a better way to debug/ parse the results than copy/pasting into excel or whatever - is there a way to pass the results of the procedure into a query? e.g., if the procedure call was something like:

exec database..proc 'arg1','arg2','arg3'

my thought was to do something like:

select distinct column1 from 
(exec database..proc 'arg1','arg2','arg3')

which clearly did not work, or I wouldn't be here. If it matters, this is for a sybase database.

Thanks!

Upvotes: 8

Views: 15387

Answers (7)

mdnttech
mdnttech

Reputation:

The only real way around this problem is to create a table in your database to store temporary values.

Lets say the stored procedures selects Column1, Column2 & Column3.

Have a table (tempTable) with Column1, Column2, Column3 and set your stored procedure to the following:

CREATE PROCEDURE database..proc
AS
BEGIN
  DELETE FROM tempTable

  INSERT INTO tempTable (Column1, Column2, Column3)
  SELECT Column1, Column2, Column3 
  FROM Table1 
END

then for your sql code to select the values have:

exec database..proc
SELECT Column1, Column2, Column3 
FROM tempTable

I hope this helps, I've come across similar problems before and this was the best I could work out.

Upvotes: 0

Yardena
Yardena

Reputation: 2847

I don't have Sybase installed right now, so some minor syntactic aspect may be wrong here - I can't check, but I used it extensively in the past: select * into #temp from proc_name('arg1','arg2','arg3') should create the local temp table for you automatically with the right columns. Within the same transaction or begin/end block you can access #temp by select * from #temp.

Upvotes: 0

Lurker Indeed
Lurker Indeed

Reputation: 1531

In SQL Anywhere 10 and 11(didn't see whether it's ASA or ASE you're asking about):

SELECT DISTINCT Column1
FROM procName(parameter1, parameter2, parameter3);

I don't have ASE, and I'm not sure if this works on earlier ASA versions.

Upvotes: 3

Evan
Evan

Reputation: 3033

I'm not familiar with Sybase, but in MySQL you could use the IN parameter to write one SQL query for all this. Ex:

select distinct column1 from table where column1 in (your_first_query_with_all_the_arguments)

Upvotes: 0

banjollity
banjollity

Reputation: 4540

Is it possible to rewrite the stored proc as a function that returns a table? On SQL Server this is certainly possible. Then you can do...

select
    <any columns you like>
from
    dbo.myFunc( 'foo', 'bar', 1 )
where
    <whatever clauses you like>
order by
    <same>

Upvotes: 1

Tom H
Tom H

Reputation: 47382

The code below works in MS SQL 2005. I don't have a Sybase installation right now to test it on that. If it works in Sybase you could use a temporary table (or permanent table) outside of your stored procedure so that you don't have alter the code that you're trying to test (not a very good testing procedure generally.)

CREATE TABLE dbo.Test_Proc_Results_To_Table
(
    my_id       INT         NOT NULL,
    my_string   VARCHAR(20) NOT NULL
)
GO

CREATE PROCEDURE dbo.Test_Proc_Results_To_Table_Proc
AS
BEGIN
    SELECT
        1 AS my_id,
        'one' AS my_string
END
GO

INSERT INTO dbo.Test_Proc_Results_To_Table (my_id, my_string)
EXEC dbo.Test_Proc_Results_To_Table_Proc
GO

SELECT * FROM dbo.Test_Proc_Results_To_Table
GO

Upvotes: 3

Learning
Learning

Reputation: 8185

you could create a temporary table (#temp) in the sp and populate the result set in there. You can later select from the same temp table from the same session. (Or use a global temp table in sybase with ##temp syntax)

This is because what you want to do (select * from exec sp) is not directly possible in sybase

Upvotes: 1

Related Questions