Get the id selected in an SP executed in an other SP
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?
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.
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.
精彩评论