CAMPzxzxDeathzxzx
asked on
SQL - Best way to write this SP
I'm looking for the best way to write this as a stored procedure.
DECLARE @ContainerName VarChar(100)
DECLARE @ContainerID Int
DECLARE @CompanyID Int
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @ContainerName = ''
SET @ContainerID = ''
SET @CompanyID = ''
SET @StartDate = ''
SET @EndDate = ''
DELETE FROM [dbo].[AuditGas] WHERE [Audit] Like '%' + @ContainerName + '%' AND [CompanyID] = @CompanyID AND [DateStamp] BETWEEN @StartDate and @EndDate
Delete FROM [dbo].[ContainerEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[ContainerNameplateCapacityChanges] WHERE [ContainerID] = @ContainerID AND [ChangeDate] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[ContainerSpecs] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[CurrentGasWeight] WHERE [ContainerID] = @ContainerID AND [ChangeDate] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[Emission] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[WarningEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate
DELETE FROM [dbo].[WeighingEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate
ASKER
I'll be passing the parameters and there will be no empty strings.
I'm looking for opinions to answer things like using between.
I thought somebody would recommend adding start / end to each delete.
Best = save me from making mistakes that experience has taught you:)
Bottom Line: If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
I'm looking for opinions to answer things like using between.
I thought somebody would recommend adding start / end to each delete.
Best = save me from making mistakes that experience has taught you:)
Bottom Line: If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
>I'll be passing the parameters and there will be no empty strings.
Ok, but your code SET all variables to empty strings '', and that's why I commented on that.
>I'm looking for opinions to answer things like using between.
Read SQL Expert Paul Maxwell's article Beware of Between. In general it's a best practice to use this instead (change the names to meet your situation)
>Best = save me from making mistakes that experience has taught you:)
Understand, but please also understand that this is not the most actionable question, as essentially it's a 'look at my code and tell me stuff', which means it's hard to tell when the question is completely answers. Much better to ask specific questions. Check out Top 10 Ways to Ask Better Questions, Number 10 for some added commentary.
>If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.
Ok, but your code SET all variables to empty strings '', and that's why I commented on that.
>I'm looking for opinions to answer things like using between.
Read SQL Expert Paul Maxwell's article Beware of Between. In general it's a best practice to use this instead (change the names to meet your situation)
WHERE dtStart >= @some_start_date AND dtEnd < @some_end_date
>Best = save me from making mistakes that experience has taught you:)
Understand, but please also understand that this is not the most actionable question, as essentially it's a 'look at my code and tell me stuff', which means it's hard to tell when the question is completely answers. Much better to ask specific questions. Check out Top 10 Ways to Ask Better Questions, Number 10 for some added commentary.
>If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.
ASKER
>If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.
>>>> Your first answer did not recommend -
I do appreciate your help.
That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.
>>>> Your first answer did not recommend -
WHERE dtStart >= @some_start_date AND dtEnd < @some_end_date
I do appreciate your help.
ASKER
What do you think?
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_ContainerRollbackBetweenDates]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ContainerRollbackBetweenDates];
GO
CREATE PROCEDURE [dbo].[usp_ContainerRollbackBetweenDates]
(
@ContainerName VarChar(100),
@ContainerID Int,
@CompanyID Int,
@StartDate DateTime,
@EndDate DateTime
)
AS
BEGIN
DELETE FROM [dbo].[AuditGas] WHERE [Audit] Like '%' + @ContainerName + '%' AND [CompanyID] = @CompanyID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
Delete FROM [dbo].[ContainerEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
DELETE FROM [dbo].[ContainerNameplateCapacityChanges] WHERE [ContainerID] = @ContainerID AND [ChangeDate] >= @StartDate AND [ChangeDate] < @EndDate
DELETE FROM [dbo].[ContainerSpecs] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
DELETE FROM [dbo].[CurrentGasWeight] WHERE [ContainerID] = @ContainerID AND [ChangeDate] >= @StartDate AND [ChangeDate] < @EndDate
DELETE FROM [dbo].[Emission] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
DELETE FROM [dbo].[WarningEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
DELETE FROM [dbo].[WeighingEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Couple of thoughts... = All Good! Thnaks
ASKER
Thanks
Thanks for the grade. Good luck with your SP. -Jim
If you mean parameterize the variables...
Open in new window
Couple of observations..