/************************************************************************************/
/* FILENAME : products.clsObjProductsTbl_Update.sql */
/* TYPE : SQL */
/* DESCRIPTION : Stored Procedure to update a record into the products table */
/* */
/* AUTHOR : Michael D Lueck */
/* mlueck@lueckdatasystems.com */
/* */
/* NEEDS : */
/* */
/* USAGE : id */
/* authid */
/* logtimestamp */
/* title */
/* */
/* RETURNS : */
/* */
/* REVISION HISTORY */
/* */
/* DATE REVISED BY DESCRIPTION OF CHANGE */
/* ---------- ---------- ------------------------------------------------------- */
/* 09/08/2011 MDL Initial Creation */
/* 09/13/2011 MDL Added missed productnumber column */
/* 09/19/2011 MDL Adjusted length of productnumber up to 25 length */
/* 12/19/2011 MDL Set default values for non-required parameters */
/* 06/06/2012 MDL Added prodteamid/eau cols */
/************************************************************************************/
-- Define name of STORED PROCEDURE for this script
:setvar SPNAME clsObjProductsTbl_Update
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = '$(SPNAME)')
DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
-- Add the parameters for the stored procedure here
@id AS smallint,
@authid AS smallint,
@logtimestamp AS datetime2(0),
@projectid AS smallint,
@prodteamid AS smallint,
@title AS varchar(100),
@productnumber AS varchar(25) = NULL,
@eau AS int,
@bomcad AS varchar(MAX) = NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRAN
UPDATE [dbo].[products]
SET [authid] = @authid,
[logtimestamp] = CURRENT_TIMESTAMP,
[projectid] = @projectid,
[prodteamid] = @prodteamid,
[title] = @title,
[productnumber] = @productnumber,
[eau] = @eau,
[bomcad] = @bomcad
WHERE [id] = @id
AND [logtimestamp] = @logtimestamp;
COMMIT TRAN
--SET NOCOUNT OFF
END