How to pass auto generated field to a store proc? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to pass auto generated field to a store proc?

Author

Topic

elistech
Starting Member

3 Posts

Posted - 2006-08-30 : 15:30:03

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-

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-30 : 15:40:26

quote:
Originally posted by elistech

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-

set @new_Id = SCOPE_IDENTITY()

________________________________________________
I am a man, I can change, if I have to, I guess.

elistech
Starting Member

3 Posts

Posted - 2006-08-30 : 16:50:11

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
----------------------------------------------------------------------

elistech
Starting Member

3 Posts

Posted - 2006-08-31 : 13:07:27

----------RESOLVED---------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 17:57:48

I hope that the solution was NOT to send the variable name. Drop the ' and just pass the value of the variable.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources