October 4, 2011 at 8:48 am
Declare @X as nvarchar(1000)
exec cpas_CloneBoM 365,845,-1,0,@X
SELECT @X
The return value "@X, or @OverlappingItems within the SPROC"
Returns NULL, but PRINTs correctly (from inside the strored proc):
32 of 1 INCH BROTHER P-TOUCH....
(1 row(s) affected)
Shouldn't it return the same as it PRINTs? I plan to use the return for a messagebox within the calling program.
USE [CPASD]
GO
/****** Object: StoredProcedure [dbo].[cpas_CloneBoM] Script Date: 10/03/2011 18:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[cpas_CloneBoM]
-- Add the parameters for the stored procedure here
@NewBoMIDInteger
,@ModelBoMIDInteger
,@UseCurrentItemInfoInteger = 0--True
,@RejectDupsInteger = -1--False
,@OverlappingItemsnvarchar(1000)Output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Collect the overlapping Items, however they're handled later...to pass back to the caller in @OverlappingItems
SELECT @OverlappingItems = COALESCE(@OverlappingItems+', ', '') + (CAST(NewBoM.BoMEItemQty AS varchar(6))) + ' of ' + NewBoM.BoMItemDescription
FROM tblBoMDetail AS NewBoM INNER JOIN
tblBoMDetail AS ModelBoM ON NewBoM.BoMItemID = ModelBoM.BoMItemID
WHERE (NewBoM.BoMID = @NewBoMID) AND (ModelBoM.BoMID = @ModelBoMID)
PRINT@OverlappingItems
--If RejectDups is FALSE, Update Quantities where the same Item is in both BoMs
UPDATE NewBoM
SET NewBoM.bomeitemqty = NewBoM.bomeitemqty + modelbom.bomeitemqty
FROM tblbomdetail AS NewBoM
INNER JOIN tblbomdetail AS modelbom
ON NewBoM.bomitemid = modelbom.bomitemid
WHERE ( @RejectDups != 0 )--Kills the whole statement if @RejectDups is True
AND ( NewBoM.bomid = @NewBoMID )
AND ( modelbom.bomid = @ModelBoMID )
-- Copy over the non-dup entries
INSERT INTO tblBoMDetail
(BoMID,
BoMItemDescription,
BoMEItemQty,
BoMEItemfactor,
BoMEItemUOM,
BoMItemUnitMaterialCost,
BoMItemUnitLaborHrs,
BoMlaborComplexity,
BoMvendorName,
BoMvendorPartNbr,
BoMItemID)
SELECT @NewBoMID,
ModelBoM.BoMItemDescription,
ModelBoM.BoMEItemQty,
ModelBoM.BoMEItemfactor,
ModelBoM.BoMEItemUOM,
ModelBoM.BoMItemUnitMaterialCost,
ModelBoM.BoMItemUnitLaborHrs,
ModelBoM.BoMlaborComplexity,
ModelBoM.BoMvendorName,
ModelBoM.BoMvendorPartNbr,
ModelBoM.BoMItemID
FROM tblBoMdetail AS ModelBoM
LEFT OUTER JOIN tblBoMdetail AS NewBoM
ON ModelBoM.BoMItemid = NewBoM.BoMItemid
WHERE ( NewBoM.BoMDetailID IS NULL )
AND ( NewBoM.BoMID = @NewBoMID )
AND ( ModelBoM.BoMID = @ModelBoMID )
--Now, update all the BoM entries with Item Master Data
UPDATE tblBoMDetail
SET BoMItemDescription=tblitemmaster.itemdescription
,[BoMEItemUOM]=tblitemmaster.itemeuom
,[BoMEItemFactor]=tblitemmaster.itemeuomfactor
,[BoMItemUnitMaterialCost]=tblitemmaster.itemmaterialstdunitcost
,[BoMItemUnitLaborHrs]=tblitemmaster.itemlaborstdunithours
FROM tblItemMaster
WHERE(@UseCurrentItemInfo = 0)
AND (tblBoMDetail.BoMItemID=tblItemMaster.ItemID)
AND (tblBoMDetail.BoMItemID=@NewBoMID)
END
GO
Jim
October 4, 2011 at 8:52 am
Declare @X as nvarchar(1000)
exec cpas_CloneBoM 365,845,-1,0,@X OUTPUT
SELECT @X
October 4, 2011 at 9:03 am
Perfect, thanks Ninja.
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply