mardi 7 mars 2017

Stored Procedure using IF

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