question

tyler.canine avatar image
tyler.canine asked

Need assistance modifying stored procedure SQL Server Management Studio

I am new to SQL. I need to alter an existing stored procedure. It currently requests 2 inputs for date and an equipment ID. It then uses the customer table to determine work week. I need to remove the input and have it run against all customers and for the current date. I am completely lost on this. Below is the current code. Any suggestions would be greatly appreciated. `enter code here`USE [RS_REPORT_DEV] GO /****** Object: StoredProcedure [dbo].[sp_DateWrite] Script Date: 2/7/2017 9:36:51 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO -- ============================================= -- Author: Tyler Canine -- Create date: 02/07/2017 -- Description: Pre-populates working weeks based on customer working week parameters. -- ============================================= ALTER PROCEDURE [dbo].[sp_DateWrite] @TargetDate datetime, @EquipID int AS BEGIN SET NOCOUNT ON; EXEC sp_EventLogInsert 'SP Start', 'sp_DateWrite', 'EXEC sp_DateWrite %1, %2', 'MTBF', 2, @TargetDate, @EquipID DECLARE @ERRORLOG as varchar(100) DECLARE @_CustomerID as int DECLARE @Ver_DateWrite as tinyint DECLARE @StartDay as tinyint DECLARE @StartDate as datetime DECLARE @EndDate as datetime DECLARE @YearRollover as tinyint DECLARE @sPeriod1 as datetime DECLARE @sPeriod2 as datetime DECLARE @YearStart as datetime DECLARE @WorkingYear as varchar(2) DECLARE @WorkingWeek as varchar(2) DECLARE @WWname as varchar(6) DECLARE @Counter as tinyint SELECT @_CustomerID=CustID FROM EquipIndex WHERE EquipID=@EquipID; SELECT @Ver_DateWrite=Ver_DateWrite, @StartDay=StartDay, @YearRollover=YearRollover FROM CustomerList WHERE CustID = @_CustomerID; SELECT @StartDate = CONVERT(date, DATEADD(day, -DATEPART(weekday, @TargetDate) + @StartDay, @TargetDate)) IF @Ver_DateWrite = 1 BEGIN --START DateWrite version 1 SET @Counter = 0 WHILE @Counter < 52 BEGIN SET @Counter = @Counter + 1 SET @sPeriod1 = DATEADD(day, -(7 * (@Counter-1)), @StartDate) SET @sPeriod2 = DATEADD(SECOND, -1, DATEADD(day, 7, @sPeriod1)) IF @YearRollover = 0 BEGIN SET @YearStart = CONVERT(datetime,cast(datepart(year, @sPeriod2) as varchar)+'/01/01') SET @WorkingYear = RIGHT(datepart(year, @sPeriod2),2) SET @WorkingWeek = cast(DATEDIFF(WW, @YearStart, @sPeriod2)+1 as varchar) END ELSE IF @YearRollover = 1 BEGIN SET @YearStart = CONVERT(datetime, cast(datepart(year, @sPeriod1) as varchar) + '/01/01') SET @WorkingYear = RIGHT(datepart(year, @sPeriod1),2) SET @WorkingWeek = DATEDIFF(WW, @YearStart, @sPeriod1) + 1 END IF LEN(@WorkingWeek) = 1 BEGIN SET @WorkingWeek = '0' + @WorkingWeek END SET @WWname = 'WW' + @WorkingYear + @WorkingWeek --SELECT WWname, CustID FROM WorkWeekTable WHERE CustID=@_CustomerID AND WWname=@WWname IF EXISTS (SELECT WWname, CustID FROM WorkWeekTable WHERE CustID=@_CustomerID AND WWname=@WWname) BEGIN UPDATE WorkWeekTable SET StartDay=@sPeriod1, EndDay=@sPeriod2 WHERE CustID=@_CustomerID AND WWname=@WWname END ELSE BEGIN INSERT INTO WorkWeekTable (WWname, StartDay, EndDay, CustID) VALUES (@WWname, @sPeriod1, @sPeriod2, @_CustomerID) END`enter code here` END END --END DateWrite version 1 --(SERVER LOG) EXEC sp_EventLogInsert EventType, EventProc, EventText, EventSys, EventSrc, LogLevel`enter code here` EXEC sp_EventLogInsert 'SP End', 'sp_DateWrite', 'EXEC sp_DateWrite %1, %2', 'MTBF', 2, @TargetDate, @EquipID END
sql-server-2008stored-procedureshelp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.