Working with results from a Stored Procedudure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Working with results from a Stored Procedudure

Author

Topic

GlynD02
Starting Member

13 Posts

Posted - 2007-11-02 : 13:44:41

I am working with a complex Stored Procedure which I need to be able to filter and sort by various criteria. Can I please ask how I can get the data that the SP returns into place that I can apply my search criteria?

Ive tried the code below in the SQL Query Analyzer but all I get is:
"Server: Msg 197, Level 15, State 1, Line 10
EXECUTE cannot be used as a source when inserting into a table variable."

DECLARE @TempPropLeads TABLE (
LeadNumber int,
LeadSource char(20) )

INSERT @TempPropLeads (LeadNumber, LeadSource)

EXEC dbo.usp_ReportProposalFromLead NULL ,'2004-06-01', '2004-06-02', NULL

SELECT 	*
FROM @TempPropLeads
ORDER BY LeadNumber DESC

GO

PS there are more fields returned from the SP - just using the above as a test.

Once I have got the data filtered/sorted as needed it's going to go into a Excel CSV spreadsheet

Cheers

TallCowboy0614
Starting Member

17 Posts

Posted - 2007-11-02 : 13:51:26

I suppose it depends on what you want to do with the data and how permanent it needs to be. Regardless, what I would do is to have the stored procedure return a table variable so that the caller can just peruse the table as necessary in it's own local table variable. In fact, it need not necessarily even be stored in a local variable but the output can be directly manipulated in the calling statement as if it was a "real" table in the database.

_________________________________
aka "Paul"
Non est ei similis.

"He's not the Messiah. He's a very naughty boy!" - Brian's mum

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 13:52:26

if possible the first choice would be to put the query from the sp into a view
if that is not possible and you are up against a wall, then put the sp results into a temp (#) table

GlynD02
Starting Member

13 Posts

Posted - 2007-11-02 : 14:01:54

Thanks for answering guys...

Do I need to put the stuff into the actual SP itself to get it into a view or temp table?

Cheers

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 14:20:29

if select statement from dbo.usp_ReportProposalFromLead can be placed in a view, you can turn dbo.usp_ReportProposalFromLead into a simple select from that view and also in the filter/search sp write your queries against that same view. hope that makes sense

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 14:57:15

"[/i]INSERT @TempPropLeads (LeadNumber, LeadSource)
EXEC dbo.usp_ReportProposalFromLead NULL ,'2004-06-01', '2004-06-02', NULL[/i]"

You cannot insert the output of an Sproc into a @TableVar (but you can insert it into a table or a #TempTable)

Kristen

GlynD02
Starting Member

13 Posts

Posted - 2007-11-02 : 16:34:38

That worked a treat, thank you Kristen!

I did have to make the temporary table columns compatible with the results (in regards to the data type) that the Stored Procedure was returning. No help in the SP as it did not define any - it just used the source column data type...

Sometimes the SQL error messages can be a bit cryptic and other times they are easy to follow!

Now I can set about filtering my data!

Cheers again

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 17:39:42

If you don't know what the datatypes of the columns returned by the Sproc are you can use OPENQUERY to run the Sproc and store the results into a table using

SELECT * INTO #TEMP FROM OPENQUERY(...)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources