New Stored Procedure Issues SS2k5

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 3, 2011 at 5:19 pm

  #243971

  I'm creating a stored procedure. It "clones" bills of material. It has four params:

  @NewBoMID (the ID of the "Target" Bill of Material that will have the "clone" added to it....

  @ModelBoMID (the ID of the "Model" Bill of Material we want to duplicate (source)

  @UseCurrentItemInfo (if true, updates costs, etc. from the Item Master)

  @RejectDups (if true, rejects any part numbers already in the "Target" Bill. If false, adds quantities)

  So, I figured out there's no Boolean data type...

  But, I get a syntax error right after the word "SET" (the "AS" clause....)????

  Anyone like a better strategy?

  I want to set this up as a roll-backable transaction. How do I do that?

  Thanks for looking

  -- ================================================

  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

  @NewBoMID Integer

  ,@ModelBoMID Integer

  ,@UseCurrentItemInfo Integer = 0--True

  ,@RejectDups Integer = -1--False

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON

  --If RejectDups is FALSE, Update Quantities where the same Item is in both BoMs

  UPDATEtblBoMDetail AS NewBoM INNER JOIN

  tblBoMDetail AS ModelBoM ON NewBoM.BoMItemID = ModelBoM.BoMItemID

  SETNewBoM.BoMEItemQty = NewBoM.BoMEItemQty + ModelBoM.BoMEItemQty

  WHERE (@RejectDups != 0)

  AND (NewBoM.BoMID = @NewBoMID)

  AND (ModelBoM.BoMID = @ModelBoMID)

  -- Copy over the non-dup entries

  INSERT INTO NewBoM

  SELECT 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

• bkubicek

  SSChampion

  Points: 10948

  More actions

  October 3, 2011 at 6:34 pm

  #1389915

  JimS-Indy (10/3/2011)

  ---

  I'm creating a stored procedure. It "clones" bills of material. It has four params:

  @NewBoMID (the ID of the "Target" Bill of Material that will have the "clone" added to it....

  @ModelBoMID (the ID of the "Model" Bill of Material we want to duplicate (source)

  @UseCurrentItemInfo (if true, updates costs, etc. from the Item Master)

  @RejectDups (if true, rejects any part numbers already in the "Target" Bill. If false, adds quantities)

  So, I figured out there's no Boolean data type...

  But, I get a syntax error right after the word "SET" (the "AS" clause....)????

  Anyone like a better strategy?

  I want to set this up as a roll-backable transaction. How do I do that?

  Thanks for looking

  -- ================================================

  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

  @NewBoMID Integer

  ,@ModelBoMID Integer

  ,@UseCurrentItemInfo Integer = 0--True

  ,@RejectDups Integer = -1--False

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON

  --If RejectDups is FALSE, Update Quantities where the same Item is in both BoMs

  UPDATEtblBoMDetail AS NewBoM INNER JOIN

  tblBoMDetail AS ModelBoM ON NewBoM.BoMItemID = ModelBoM.BoMItemID

  SETNewBoM.BoMEItemQty = NewBoM.BoMEItemQty + ModelBoM.BoMEItemQty

  WHERE (@RejectDups != 0)

  AND (NewBoM.BoMID = @NewBoMID)

  AND (ModelBoM.BoMID = @ModelBoMID)

  -- Copy over the non-dup entries

  INSERT INTO NewBoM

  SELECT 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

  Your update is a bit off, try this instead:

  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)

  AND (NewBoM.BoMID = @NewBoMID)

  AND (ModelBoM.BoMID = @ModelBoMID)

  To have it in a transaction you should google sql server begin tran

• hunt

  Right there with Babe

  Points: 780

  More actions

  October 3, 2011 at 10:58 pm

  #1389938

  Sql server is having Boolean Data Type, declare the vairable as bit

  You can use the below

  ALTER PROCEDURE [dbo].[cpas_CloneBoM]

  -- Add the parameters for the stored procedure here

  @NewBoMID Integer

  ,@ModelBoMID Integer

  ,@UseCurrentItemInfo bit= 1 --True

  ,@RejectDups bit= 0 --False

  AS

  BEGIN

  //Your logic

  END

  thanks
  sarat 🙂
  Curious about SQL

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 4, 2011 at 7:38 am

  #1390136

  hunt (10/3/2011)

  ---

  Sql server is having Boolean Data Type

  Just to be 100% clear sql does not have a boolean but a bit data type is a decent replacement. A boolean is either True or False (2 possible values). A bit is really an integer datatype that can have 3 values. 0, 1, null. Not trying to split hairs but if you are unaware that it can contain null it can come back to bite you.

  http://msdn.microsoft.com/en-us/library/ms177603.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 4, 2011 at 7:46 am

  #1390141

  Since my user interface is Access, I'm gonna use Integer and use the values Access uses (0=true, anything else i.e. -1=false) It's just easier. I'll set a default to avoid null.

  Thanks for the advice....

  Jim

• tfifield

  SSCrazy Eights

  Points: 9655

  More actions

  October 5, 2011 at 11:56 am

  #1390867

  Actually Access treats 0 as false and -1 as true.

  Todd Fifield

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 5, 2011 at 12:20 pm

  #1390876

  JimS-Indy (10/4/2011)

  ---

  I'll set a default to avoid null.

  If the column mustn't be null, define the column as NOT NULL, otherwise default or no default, nulls can go in.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 5, 2011 at 1:58 pm

  #1390924

  Yeah, Todd, I figured that out the hard way today. d'ooohhhh

  Jim

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 5, 2011 at 1:59 pm

  #1390925

  Not a column. It's a parameter....

  Jim