I need some help, I am somewhat still new to SQL. I have a stored procedure that is executed from another application as a smartobject. My code before te changes look like the code in Section 1. I changed the code as per Section 2 so that the stored procedure would first look if the Sales id type was in fact 3 comparing with the membershipnumber in the table memberships from the stored procedure and then do the same in the members table. If the query was 3 with the membership number I wanted it to use the expiry date in the members table and not the section
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, 0, @CreatedDate)))
That woudl add 364 days to current date
Section1
USE [HotelStayWorkflow]
GO
/****** Object: StoredProcedure [dbo].[CP_Create_Card] Script Date: 3/7/2017 2:50:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CP_Create_Card]
-- Add the parameters for the stored procedure here
@membershipNumber VARCHAR(MAX),
@CardId BIGINT = NULL OUTPUT,
@minsToFirstDeadline int = 2880,--defailt 2 days
@minsToSecondDeadline int = 5760 --default 4 days
AS
BEGIN
DECLARE @CreatedDate DATETIME = GETDATE()
DECLARE @ExpiryDate DATETIME
DECLARE @FirstDeadine DATETIME
DECLARE @SecondDeadline DATETIME
BEGIN
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, 0, @CreatedDate)))
END
SELECT @FirstDeadine = DATEADD(MINUTE,@minsToFirstDeadline,GETDATE())
SELECT @SecondDeadline = DATEADD(MINUTE,@minsToSecondDeadline,GETDATE())
--Create Card Linked to Membership below
IF EXISTS(SELECT * FROM Cards WHERE MembershipNumber = @membershipNumber AND Enabled = 1)
BEGIN
UPDATE Cards SET Enabled = 0 WHERE MembershipNumber = @membershipNumber AND Enabled = 1
END
INSERT INTO Cards (CardStatusId, MembershipNumber, PrintedDate, Created, Modified, ExpiryDate,FirstDeadline,SecondDeadline, Enabled)
VALUES(1, @membershipNumber, @CreatedDate, @CreatedDate, @CreatedDate, @ExpiryDate,@FirstDeadine,@SecondDeadline, 1)
SET @CardId = SCOPE_IDENTITY()
-- Return Card Id
END
Section 2
USE [HotelStayWorkflow]
GO
/****** Object: StoredProcedure [dbo].[CP_Create_Card] Script Date: 3/7/2017 1:08:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CP_Create_Card]
-- Add the parameters for the stored procedure here
@membershipNumber VARCHAR(MAX),
@CardId BIGINT = NULL OUTPUT,
@minsToFirstDeadline int = 2880,--defailt 2 days
@minsToSecondDeadline int = 5760 --default 4 days
AS
BEGIN
DECLARE @CreatedDate DATETIME = GETDATE()
DECLARE @ExpiryDate DATETIME
DECLARE @FirstDeadine DATETIME
DECLARE @SecondDeadline DATETIME
BEGIN
IF EXIST (FROM Memberships WHERE MembershipNumber = @MembershipNumber AND SalesTypeId = 3 AND Enabled =1)
AND
IF EXIST (FROM Members WHERE MembershipNumber = @MembershipNumber AND Enabled =1)
SELECT @ExpiryDate = dbo.Members.MembershipExpiry
OR
SELECT @ExpiryDate = DATEADD(YEAR, 1, DATEADD(d, -1, DATEADD(m, 0, @CreatedDate)))
END
SELECT @FirstDeadine = DATEADD(MINUTE,@minsToFirstDeadline,GETDATE())
SELECT @SecondDeadline = DATEADD(MINUTE,@minsToSecondDeadline,GETDATE())
--Create Card Linked to Membership below
IF EXISTS(SELECT * FROM Cards WHERE MembershipNumber = @membershipNumber AND Enabled = 1)
BEGIN
UPDATE Cards SET Enabled = 0 WHERE MembershipNumber = @membershipNumber AND Enabled = 1
END
INSERT INTO Cards (CardStatusId, MembershipNumber, PrintedDate, Created, Modified, ExpiryDate,FirstDeadline,SecondDeadline, Enabled)
VALUES(1, @membershipNumber, @CreatedDate, @CreatedDate, @CreatedDate, @ExpiryDate,@FirstDeadine,@SecondDeadline, 1)
SET @CardId = SCOPE_IDENTITY()
-- Return Card Id
END
Aucun commentaire:
Enregistrer un commentaire