can i run a stored proc (and use its return value) in an insert

[jasperjasper]

I have a stored proc that returns an @@IDENTITY...
I want that proc to be kind of a black box...using its return value in the insert....can this be done....

Thanks

 

[SinThetik]

Hi Jasper,
There are many ways to skin a cat, and here are 2. For your purposes I would really recommend using the function.. though it would depend where you're getting your @@Identity from, I assume your proc must do something?

Functions can be limiting, if the function doesn;t work for you, try the procedure method...

Cheers!


If Exists(Select * from sysobjects where name = 'PR_Return')
Begin
Drop Procedure PR_Return
End
GO

CREATE PROCEDURE PR_Return(@Return_Me Int OUTPUT)
AS

SELECT @Return_Me = 3
RETURN

Go

CREATE Function fn_Return (@Var VarChar(10))
RETURNS VarChar(10)
AS
BEGIN
DECLARE @OutVar VarChar(10)
SELECT @OutVar = @Var
RETURN (@OutVar)
END
Go

Select

If exists(Select * from sysobjects where name = 'tb_InsertMyReturn')
Begin
Drop Table tb_InsertMyReturn
End

Create Table tb_InsertMyReturn (Value Int, VarValue VarChar(10))
Go

DECLARE @Return Int
Exec PR_Return @Return OUTPUT

Insert tb_InsertMyReturn Values (@Return, 'Use A Variable')
Insert tb_InsertMyReturn Select 0, dbo.fn_Return('Use Func')


Select * from tb_InsertMyReturn

 

[jasperjasper]

Here is my insert statement followed by the values...
for certain values, I want to use the return value from the a sp (which is a identity value) and use that in my insert
......


INSERT into Pickups (Pickup, PUTimFrTu, PUTimToTu,PUTimFrWe,PUTimToWe, PUTimFrTh, PUTimToTh, PUTimFrFr, PUTimToFr, PUTimFrSa, PUTimToSa, PUTimFrSo, PUTimToSo,
PUOnFr, PUOnThu, PUOnSa, PUOnSo, BusnType, UseTruck, NeedApptm, InclHoopCarrNot,
Scale, PUVMCntcID, ScaleType, ScaleAddr, PUOnMon, PUOnTue, PUOnWed, ShortSiding, IntTranNotes, CarrNotNotes, UseRailcar, UseVessel, PickupVerMeth, SlsBuyCntcID, SlsMLev1CntcID, SlsMLev2CntcID, SlsShpCntcID,
MakeHeavyLoads, HasBaler, HarmMillBuyer, IntSlsNotes, HowNotifyDowngr, DowngrCntcID, PUNoType, PUNoCntcId, Tran1CntcID, SendMethTran2, Tran2CntcID, PUTimFrMo, PUTimToMo)


Values ( @mcAccount,@PuTimFrTuPU, @PuTimToTuPU, @PuTimFrWePU, @PuTimToWePU, @PuTimFrThPU, @PuTimToThPU, @PuTimFrFrPU, @PuTimToFrPU, @PuTimFrSaPU,@PuTimToSaPU,
@PuTimFrSoPU, @PuTimToSoPU, @PuOnFriPU, @PuOnThuPU, @PuOnSatPU,@PuOnSunPU,@BusnTypePU, @UseTruckPU, @NeedApptmPU, @InclHoopCarrPU, @ScalePU,
@PuvmCntcIDPU,@ScaleTypePU, @ScaleAddrPU, @PuOnMonPU, @PuOnTuePU,@PuOnWedPU, @ShortSidingPU, @IntTranNotesPU, @CarrNotNotesPU, @UseRailCarPU,@UseVesselPU, @PickUpVerMethPU, @SlsMLev1CntcIDPU, @SlsMLev2CntcIDPU, @SlsShpCntcIDPU, @SlsBuyCntcIDPU,
@MakeHeavyLoadsPU, @HasBalerPU, @HarmMillBuyerPU, @IntSlsNotesPU, @HowNotifyDownGrPU, @DownGrCntcIDPU, @PUNoTypePU, @PUNoCntcIdPU, @Tran1CntcIDPU, @SendMethTran2PU, @Tran2CntcIDPU,@PuTimFrMoPU, @PuTimToMoPU)