Exec sp's within select statement

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)

Exec sp's within select statement

Author

Topic

DavidRhodes
Starting Member

40 Posts

Posted - 2004-10-08 : 11:05:01

What I wish to achieve is the following (in red), obviously this doesn't work. The stored proc spItemSalePrice is quite complex and needs to be seperated for other functionality. I don't want to duplicate the code inside this stored proc. Can this be achieved?

CREATE PROCEDURE [spItemSpecialOfferList]

@SiteID int

AS

SELECT 
	i.*, 
	(([Price]/[SalePrice]) * 100) AS [Discount],
	m.[Name] AS [Manufacturer],
        EXEC spItemSalePrice i.[ItemID], @SiteID

FROM [Items] i

INNER JOIN [Manufacturers] m ON m.[ManufacturerID] = i.[ManufacturerID]

WHERE i.[Enabled] = 1 AND i.[SpecialOffer] = 1

ORDER BY [Discount] DESC