results of one query used by another (1 SP)

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

results of one query used by another (1 SP)

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-14 : 09:48:57

Hi guys,

I'm wondering if i can use the result of one basic query, as a condition from another query and return the result all within one store procedure. An example is below


CREATE PROCEDURE dbo.Example
//take in the orderItemID
@orderItemID    INT
	
AS
DECLARE @inventoryID int
DECLARE @SerialNum int

BEGIN TRANSACTION getSerialNum
//use the orderItemID to get the inventoryID
		SELECT InventoryID
		FROM tblOrderItems
		WHERE orderItemID = @orderItemID

//then use the inventoryID from above query as condition to get SerialNum		
		SELECT SerialNum
		FROM tblInventory
		WHERE inventoryID = ???
//then return the serialnum			
RETURN @SerialNum			
GO

SO can this be done and what do i need to put where the ??? are to do it?

thanks for any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 09:52:43

SELECT SerialNum
FROM tblInventory
WHERE inventoryID in (SELECT InventoryID
FROM tblOrderItems
WHERE orderItemID = @orderItemID)

Madhivanan

Failing to plan is Planning to fail

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-14 : 09:56:10

Thanks for the reply Madhivanan.

so that query will get SerialNum. But how do i return that from the SP?

Is it just "RETURN SerialNum"?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 09:56:23

unless i'm missing something obvious...

select @serialNum = SerialNum
from tblInventory i join o tblOrderItems on i.inventoryID = o.inventoryID
where orderItemID = @orderitemID

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:21:42

or simply add a new output parameter @serialNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources