use a stored procedure as part of a query

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

use a stored procedure as part of a query

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-04-14 : 19:10:51

is there any way to feed the results of a stored procedure into selects on a query???

for example something like this:

select * from mytable where zip in (exec radius @zip=29577,@distance=35)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 19:34:31

Dump the results of the stored proc into a temp table, like this:



CREATE PROCEDURE uspTestProc
@param INT
AS 

SELECT @param * 1 AS val
UNION ALL SELECT @param * 2
UNION ALL SELECT @param * 3
GO

CREATE TABLE #tmp (v INT)

INSERT INTO #tmp
EXEC uspTestProc 2

SELECT * 
FROM Production.Product p
INNER JOIN #tmp t
	ON p.ProductID = t.v
GO

DROP TABLE #tmp
GO
DROP PROCEDURE uspTestProc
GO

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-04-14 : 19:42:11

only thing is the query changes ALL the time... and can have any number of other selects in the where clause...

is there any way to just have SQL Join against the results or something? rather than dumping to a table, than joining against that...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 08:32:14

Refer method 2
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/24/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-04-15 : 12:23:20

Perfect, thanks a lot!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 13:56:36

quote:
Originally posted by madhivanan

Refer method 2
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/24/select-columns-from-exec-procedure-name-is-this-possible.aspx

Be careful using OPENROWSET like this. It has the potential to create deadlock issues (See the example below). Also, if you're not using a trusted connection, then you'll have to pass it a password from within the TSQL that calls it. Finally, because you have to specify a server name, if you move the database to another server, it will continue to reference the original server.

Run this script on the AdventureWorks database to see an example of the deadlocking issue created bu OPENROWSET. Basically, it occurs because the stored procedure is executed under a different connection. it will wait for any locks held by the transaction of the first connection, which wont be released until OPENROWSET returns.

USE AdventureWorks
GO

CREATE PROCEDURE uspGetProdName
@ProductID INT
AS
	SELECT ProductID, Name 
	FROM Production.Product
	WHERE ProductID = @ProductID
GO


BEGIN TRANSACTION

UPDATE Production.Product
SET Name = 'zzzAdjustable Race'
WHERE ProductID = 1

SELECT * FROM
OPENROWSET('SQLOLEDB',
	'Data Source=YOUR_SERVER_NAME;Trusted_Connection=yes;Integrated Security=SSPI',
	'Execute AdventureWorks.dbo.uspGetProdName 1')

COMMIT TRANSACTION
GO

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 04:49:22

Thanks. I will keep that in mind

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources