[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to implements "select tmp.tagid from (exec store_procedure) tmpA"

Posted on 2005-04-18
3
Medium Priority
?
393 Views
Last Modified: 2008-03-17
Hi,
I have a procedure A which is something 'Select * from TBL A'. But procedure is very long actraully.

Another procedure will select single field from result of the procedure A.
I can repeat that exactly part in procedure A,so like procedure B become
select tmpA.tagid from (Select * from TBL A) tmpA
but I dont want to repeat the code of procedure A in Procedure B.

Is there something like--
Select tmpA.tagid from (exec procedureA) tmpA

thank you every much
0
Comment
Question by:yangbin991
3 Comments
 
LVL 9

Expert Comment

by:solution46
ID: 13806884
There is - use a user defined function. I'm not much good at them so please look in books online, or wait for somebody to tell you exactly how to do it.

s46.
0
 
LVL 7

Accepted Solution

by:
tlovie earned 180 total points
ID: 13818997
One way that I've accomplished this is to create a temporary table to hold the results of the proceure A
>>  CREATE TABLE #tmpA (Field1 int, Field2 int, ...)

Then you insert the data from the procedure into the temporary table:
>>  INSERT INTO #tmpA
>>  EXEC procedureA

Then finally select the fields you want

>>  SELECT Field1 FROM #tmpA

You can encapsulate this into another procedure, that way if you change the way the procedure A operates you don't have to maintain code in 2 places.

Hope this helps. :)
0
 

Author Comment

by:yangbin991
ID: 13830988
thank tloie.

create tmp table actrually is not good. because it should be slow down the process. But seems only this way to solve.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question