if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[swr_get_work_requ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[swr_get_work_requ] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.swr_get_work_requ ----------------------------------------------------------------------------------------- -- DESCRIPTION: Saves a Work Order Trade for a Work Order into f_WO_Trades -- Outputs: -- Two output fields -- WOT_PK int Primary Key of Work Request Trade. -- Being returned as output parm instead of recordset so it's easier -- to use value inside other stored procedures. -- RTN_CODE int 0 = success. -- $Header: X:cvsroot/SOB/Database/TMA/script/swr_get_work_requ.sql,v 1.1 2006/04/08 17:27:27 hstaples Exp $ -- $Revision: 1.1 $ -- $Date: 2006/04/08 17:27:27 $ -- $Log: swr_get_work_requ.sql,v $ -- Revision 1.1 2006/04/08 17:27:27 hstaples -- Initial CheckIn. Tested OK. -- ----------------------------------------------------------------------------------------- ( @Effective_Date datetime ) AS SELECT DECLARE @LOG_PK int DECLARE @SWR_STATUS varchar(45) SET @SWR_STATUS = '' -- TODO: Create temp table (wide enough to hold all fields in SELECT) LOG_PK, LOG_REQUESTOR, LOG_REQ_PHONE, LOG_CHAR_60_1, LOG_CHAR_30_4, LOG_CN_FK, LOG_CREATE_DATE, LOG_REQUEST, (LOG_STATUS), 'TBD' As LOG_TYPE, LOG_FU_FK, LOG_FB_PK, LOG_LT_FK, LOG_CREATE_DATE DECLARE crsLog As SELECT LOG_PK FROM f_Requests FETCH NEXT FROM crsLog INTO @LOG_PK WHILE @@FETCH_STATUS <> -1 BEGIN EXEC swr_get_work_requ_stat @LOG_PK, @SWR_STATUS out -- TODO; INSERT RECORD INTO TEMPTABLE, all other fields and the value @SWR_STATUS plugged into the field @LOG_STATUS FETCH NEXT FROM crsLog INTO @LOG_PK END CLOSE crsLog DEALLOCATE crsLog -- Return Result Set SELECT * FROM whatevertemptablenameis ORDER BY LOG_PK DROP whatevertemptablenameis -- TODO: Set Return Code SET @Rtn_Code = -990 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

Software/Hardware used: