Get the id selected in an SP executed in an other SP

Question

i have an SP that executes 1 SP at the moment

EXEC mpSPAccess.PostIdSelect @PostDate = @TodaysDate

The SP does something like this (very simplyfied :))

SELECT id FROM Post WHERE DateCreated = @PostDate

After this SP is Executed i want to use the id i got from PostIdSelect as an parameter for more SPs Like this:

EXEC mpSPAccess.GetSomethingWithThePostIdSelect @PostId = @PostIdFromTheFirstSpSELECT
EXEC mpSPAccess.GetAnotherSomethingWithThePostIdSelect @PostId = @PostIdFromTheFirstSpSELECT

Is this possible in some way?

Answer 1

You could just set up your PostIdSelect stored proc to return an int or whatever appropriate output parameter representing the ID you select, then feed that to your other procs, something like:

CREATE PROCEDURE PostIdSelect

     @PostDate datetime,
     @PostId int OUTPUT

AS

SELECT @PostID = id FROM Post WHERE DateCreated = @PostDate

GO

Then to utilize this,

DECLARE @OutputID int

EXEC PostIdSelect '1/28/2010', @OutputID

SELECT @OutputID    -- Optional, just to view the resulting output ID

EXEC GetSomethingWithThePostIdSelect @PostID = @OutputID

One note, with your initial SELECT statement, you probably want to use SELECT TOP 1 ID or something to prevent multiple values from being returned, unless you know for certain that DateCreated will be unique to every single record in the table.

Answer 2

Given a stored procedure like you indicated:

CREATE PROCEDURE PostIdSelect
   @PostDate datetime
AS
SELECT id FROM Post WHERE DateCreated = @PostDate

This can be used without modification by capturing the rowset into a table:

DECLARE @PostID int
CREATE TABLE #PostIDs (PostID int)
INSERT #PostIDs EXEC PostIdSelect @TodaysDate
SELECT @PostID = TOP 1 PostID FROM #PostIDs
-- use @PostID

Another way to do this, if you will always be returning only a single row, is to use an output parameter, which requires modifying your stored procedure:

CREATE PROCEDURE PostIdSelect2
   @PostDate datetime,
   @PostID int OUT
AS
SELECT TOP 1 @PostID = id FROM Post WHERE DateCreated = @PostDate

using it like so:

DECLARE @PostID int
EXEC PostIdSelect2 @TodaysDate, @PostID OUT
-- use @PostID

Notice that with both of these methods, you can't gloss over the idea that there could be multiple PostIDs. With the first table-insertion method, you could do something with each row individually, or even join to the table. It would be best if at all possible to join to the table, but if you must do something with each row individually, a fast-forward read-only cursor is actually faster than looping yourself.