INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to pass auto generated field to a store proc?

How to pass auto generated field to a store proc?

(OP)
Hi,

I have a stored Procedure that Adds data into SQL table from a windows form. During the "add" to the table "PlanID" field is generated automatically(by Identity Increment). I have another store procedure that I need to call in this one which needs to accept the "PlanID" that is just created and with a few more fields from this table Insert into another table that I have. Can someone please give me some direction on this or show an sample code?

Thanks
Elis-

    
--------------------------------------------------------------------------------
 
Hi,
I coppied my stored procedures below. I tried different ways to use it but it still doesn't insert to the second table at all. Can you please look at it and possibly tell me what I am doing wrong?

Thanks - Elis

------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_InsertBudgetPlan

@SAPSoldToID VARCHAR(20)
,@NumberofBudgetPymts INT
,@BudgetPymtStartDate DATETIME
,@BudgetPymtEndDate DATETIME
,@ReconInvoiceStartDate DATETIME
,@ReconInvoiceEndDate DATETIME
,@EstimUsageVolume DECIMAL
,@ProductCode VARCHAR(50)
,@EstimUnitPrice DECIMAL
,@PriorBalanceAmt DECIMAL
,@PriorBalanceDate DATETIME
,@BalanceSource VARCHAR(50)
,@BudgetPymtDueDayOfMonth INT
--,@BudgetPymtDate DATETIME
,@ReconDate DATETIME
,@DiscountAmt DECIMAL
,@DiscountFlag VARCHAR(50)
,@BudgetPymtAmt DECIMAL
,@CorrespondenceMethod VARCHAR(50)
,@StmtPackageType VARCHAR(10)
AS

DECLARE @dmpkcustsoldtokey INT


SET @dmpkcustsoldtokey = (SELECT dmpkcustsoldtoSAPkey FROM dim_CustSoldToSAP AS so WHERE so.SAPsoldtoid = @SAPSoldToID)

INSERT INTO BudgetPlan
(SAPSoldToID
,dmpkcustsoldtokey
,NumberofBudgetPymts
,BudgetPymtStartDate
,BudgetPymtEndDate
,ProductCode
,BudgetPymtAmt
--,BudgetPymtDate
,BudgetPymtDueDayOfMonth
,EstimUsageVolume
,EstimUnitPrice
,PriorBalanceAmt
,PriorBalanceDate
,BalanceSource
,ReconDate
,ReconInvoiceStartDate
,ReconInvoiceEndDate
,DiscountFlag
,DiscountAmt
,CorrespondenceMethod
,StmtPackageType)

VALUES (@SAPSoldToID
,@dmpkcustsoldtokey
,@NumberofBudgetPymts
,@BudgetPymtStartDate
,@BudgetPymtEndDate
,@ProductCode
,@BudgetPymtAmt
--,@BudgetPymtDate
,@BudgetPymtDueDayOfMonth
,@EstimUsageVolume
,@EstimUnitPrice
,@PriorBalanceAmt
,@PriorBalanceDate
,@BalanceSource
,@ReconDate
,@ReconInvoiceStartDate
,@ReconInvoiceEndDate
,@DiscountFlag
,@DiscountAmt
,@CorrespondenceMethod
,@StmtPackageType)

DECLARE @NewBudgetPlanID INT

SET @NewBudgetPlanID = SCOPE_IDENTITY()

exec dbo.usp_BPPymtDetail_insert_Variable '@NewBudgetPlanID'


GO
----------------------------------------------------------
----------------------------------------------------------
CREATE PROCEDURE dbo.usp_BPPymtDetail_insert_Variable
@NewBudgetPlanID INT,
@BudgetPymtStartDate DATETIME,
@BudgetPymtEndDate DATETIME

AS

DECLARE @insertdate DATETIME

--SET @NewBudgetPlanID = SCOPE_IDENTITY()
SET @insertdate = @BudgetPymtStartDate


WHILE @insertdate < @BudgetPymtEndDate
BEGIN
SELECT @NewBudgetPlanID,@BudgetPymtStartDate, @BudgetPymtEndDate, @insertdate
INSERT INTO BudgetPlanDetail (BudgetPlanID, BudgetPymtDate, BudgetPymtAmt)
SELECT @NewBudgetPlanID, @insertdate, BudgetPymtAmt
FROM BudgetPlan AS BP
WHERE BP.BudgetPlanID = @NewBudgetPlanID
SET @insertdate = DATEADD(month , 1, @insertdate )
END
GO
 

RE: How to pass auto generated field to a store proc?

(OP)
----------RESOLVED---------------

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close