Need Help with SELECT / EXEC - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help with SELECT / EXEC

Author

Topic

spotvader
Starting Member

1 Post

Posted - 2005-07-21 : 14:27:59

Hi I am trying to see if it is possible to select the results of an EXEC statement that runs a stored procedure into a temporary table or some other object that I can then use to join another select statement on.

For example, using the following I can execute my stored procedure via the command line and get a set of resulting records:

EXEC myStoredProcedureName @Param1

What I would like to do is run that stored procedure first like I do with that statement and then take the resulting records and join them with another set of records I get by using a simple SELECT statement.

I have tried the following, but get syntax errors:

SELECT * FROM table1
RIGHT OUTER JOIN SELECT(EXEC mySP1 @Param1) AS Q2 ON
table1.ID = Q2.ID

Any ideas on how to accomplish this?

Thanks,

spotvader

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-21 : 15:31:00

create a temp table and insert the result of the sp into it

create table #a(...)
insert #a exec mysp

then you can join to the table.
Otherwise make the sp into a function.

You can use the result of the sp in a query by using openquery but that would create a new connection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources