locked
I need only one time IsDefault should 1. If next time while inserting/Updating if isdefault is 1 then previous all isdefault should 0. RRS feed

  • Question

  • User1878568433 posted

    Here is my Stored Procedure Code.

    I need only one time IsDefault should 1.

    If next time while inserting/Updating if isdefault is 1 then previous all isdefault should 0.

    How to do.Please Suggest

    USE [OpteliDB]
    GO

    /****** Object: StoredProcedure [dbo].[prc_U_tbl_In_Client] Script Date: 7/16/2018 10:57:11 AM ******/
    DROP PROCEDURE [dbo].[prc_U_tbl_In_Client]
    GO

    /****** Object: StoredProcedure [dbo].[prc_U_tbl_In_Client] Script Date: 7/16/2018 10:57:11 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[prc_U_tbl_In_Client]
    (
    @N_Client_ID numeric=0,
    @VC_Client_Name varchar(5000),
    @VC_DBA varchar(50),
    @N_Ind_Code_IDF numeric,
    @VC_Entity_Type varchar(50),
    @VC_Speciality_Taxonomy varchar(5000),
    @VC_Address_1 varchar(75),
    @VC_Address_2 varchar(75),
    @VC_Country varchar(30),
    @VC_Zip_Code varchar(10),
    @VC_State varchar(30),
    @VC_City varchar(50),
    @VC_Phone1 varchar(20),
    @VC_Phone_Type1 varchar(15),
    @VC_Phone2 varchar(20),
    @VC_Phone_Type2 varchar(15),
    @VC_Phone3 varchar(20),
    @VC_Phone_Type3 varchar(15),
    @VC_Fax varchar(20),
    @B_Is_Chart_Auto bit,
    @VC_NPI varchar(50),
    @VC_Tax_ID varchar(50),
    @VC_CLIA varchar(50),
    @VC_AX_Client_ID varchar(50),
    @VC_Service_Code varchar(1000),
    @VC_Healing_Classification varchar(1000),
    @VC_System_Name varchar(100),
    @VC_MAC_ID varchar(100),
    @VC_ModifiedBy_IDF varchar(50),
    @SDT_Modified_ON smalldatetime,
    @VC_Client_Note varchar(5000)='',
    @VC_UID varchar(50)='',
    @VC_PWD varchar(1000)='',

    @VC_HF_Location varchar(55)='',
    @VC_Domain_Name varchar(200)='',
    @VC_Terminals_Required varchar(50)='',
    @VC_Users_Name varchar(50)='',
    @ColorCode varchar(10),
    @ColorName varchar(50),
    @IsDefault Bit = NULL,
    @chkDefault Bit = NULL,
    @VC_Disclaimer_Note as varchar (500) = null,
    @VC_LicenseNo as varchar(100)=null,
    @VC_Cds as varchar(100)=null,



    --- This is for tbl_In_Client_Alternate_Location
    @xmlSchemaClientLocation Varchar(50),
    @xmlMapClientLocation int,
    @xmlDocClientLocation nText,

    )

    AS
    DECLARE @iDoc int
    DECLARE @N_CL_ID numeric

    --ADDEDBY SUNITA ON 19TH MAY 2016

    DECLARE @VC_Speciality_p VARCHAR(5000)

    create table #tempSpeciality
    (
    n_id int primary key identity(1,1)
    ,speciality VARCHAR(500) NULL
    )

    CREATE TABLE #tempProvider
    (
    RowNum INT IDENTITY PRIMARY KEY,
    providerID VARCHAR(50) NULL
    )

    CREATE TABLE #tempRuleID
    (
    RowNum INT IDENTITY PRIMARY KEY,
    RuleID INT
    )

    create table #tempBusinessRuleIdf
    (
    BusinessRuleIDF INT
    )

    CREATE TABLE #tempProviderU
    (
    RowNum INT IDENTITY PRIMARY KEY,
    providerID VARCHAR(50) NULL
    )


    --END

    BEGIN
    SET NOCOUNT ON

    EXEC sp_xml_PrepareDocument @iDoc OUTPUT, @xmlDocClientLocation
    DELETE tbl_In_Client_Alternate_Location FROM tbl_In_Client_Alternate_Location
    LEFT JOIN
    (
    SELECT XD_IDF FROM OPENXML
    (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int
    )
    ) b on tbl_In_Client_Alternate_Location.N_ALOC_ID= b.XD_IDF
    WHERE N_Client_IDF = @N_Client_ID and b.XD_IDF is NULL

    EXEC sp_xml_RemoveDocument @iDoc

    EXEC sp_xml_PrepareDocument @iDoc OUTPUT, @xmlDocClientLocation

    update tbl_In_Client_Alternate_Location
    set

    VC_NPI=TBALTLOC. XD_NP,
    VC_CLIA=TBALTLOC.XD_CL,
    VC_Address_1=TBALTLOC.XD_ADD1,
    VC_Address_2=TBALTLOC.XD_ADD2,
    VC_Country=TBALTLOC.XD_Coun,
    VC_PO_Box=TBALTLOC.XD_PO,
    VC_Zip_Code=TBALTLOC.XD_Zip,
    VC_State=TBALTLOC.XD_Sate,
    VC_City=TBALTLOC.XD_City,
    VC_Phone1=TBALTLOC.XD_PH1,
    VC_Phone_Type1=TBALTLOC.XD_PHT1,
    VC_Phone2=TBALTLOC.XD_PH2,
    VC_Phone_Type2=TBALTLOC.XD_PHT2,
    VC_Phone3=TBALTLOC.XD_PH3,
    VC_Phone_Type3=TBALTLOC.XD_PHT3,
    VC_Fax=TBALTLOC.XD_FAX,
    VC_Name=TBALTLOC.XD_NA,
    VC_Con_Name =TBALTLOC.XD_CN,
    VC_Location_Code=TBALTLOC.XD_LocationCode,
    ColorCode=TBALTLOC.XD_ColorCode,
    ColorName=TBALTLOC.XD_ColorName,
    IsDefault= TBALTLOC.XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    from tbl_In_Client_Alternate_Location
    INNER JOIN
    (
    SELECT XD_IDF,
    XD_NP ,
    XD_CL ,
    XD_ADD1 ,
    XD_ADD2 ,
    XD_Coun ,
    XD_PO ,
    XD_Zip ,
    XD_Sate ,
    XD_City ,
    XD_PH1 ,
    XD_PHT1 ,
    XD_PH2 ,
    XD_PHT2 ,
    XD_PH3 ,
    XD_PHT3 ,
    XD_FAX,
    XD_NA ,
    XD_CN ,
    XD_LocationCode,
    XD_ColorCode,
    XD_ColorName ,
    XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    FROM OPENXML (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int,
    XD_NP varchar(50),
    XD_CL varchar(59),
    XD_ADD1 varchar(75),
    XD_ADD2 varchar(75),
    XD_Coun varchar(30),
    XD_PO varchar(10),
    XD_Zip Varchar(10),
    XD_Sate varchar(30),
    XD_City varchar(50),
    XD_PH1 varchar(20),
    XD_PHT1 varchar(20),
    XD_PH2 varchar(20),
    XD_PHT2 varchar(20),
    XD_PH3 varchar(20),
    XD_PHT3 varchar(20),
    XD_FAX varchar(20),
    XD_NA varchar(100),
    XD_CN varchar(100),
    XD_LocationCode varchar(55),
    XD_ColorCode varchar(10),
    XD_ColorName varchar(50),
    XD_IsDefaultLocation bit --Added By sripada on 12/07/2018 for Is default alternate Location
    )
    )AS TBALTLOC on TBALTLOC.XD_IDF = tbl_In_Client_Alternate_Location.N_ALOC_ID and tbl_In_Client_Alternate_Location.N_Client_IDF = @N_CL_ID
    EXEC sp_xml_RemoveDocument @iDoc

    EXEC sp_xml_PrepareDocument @iDoc OUTPUT, @xmlDocClientLocation

    INSERT INTO tbl_In_Client_Alternate_Location
    (
    N_Client_IDF,
    VC_NPI,
    VC_CLIA,
    VC_Address_1,
    VC_Address_2,
    VC_Country,
    VC_PO_Box,
    VC_Zip_Code,
    VC_State,
    VC_City,
    VC_Phone1,
    VC_Phone_Type1,
    VC_Phone2,
    VC_Phone_Type2,
    VC_Phone3,
    VC_Phone_Type3,
    VC_Fax,
    VC_Name,
    VC_Con_Name ,
    VC_Location_Code ,
    ColorCode,
    ColorName,
    IsDefault --Added By sripada on 12/07/2018 for Is default alternate Location

    )

    SELECT @N_CL_ID,
    XD_NP,
    XD_CL,
    XD_ADD1,
    XD_ADD2,
    XD_Coun,
    XD_PO,
    XD_Zip,
    XD_Sate,
    XD_City,
    XD_PH1,
    XD_PHT1,
    XD_PH2,
    XD_PHT2,
    XD_PH3,
    XD_PHT3,
    XD_FAX,
    XD_NA,
    XD_CN,
    XD_LocationCode,
    XD_ColorCode,
    XD_ColorName ,
    XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    FROM OPENXML
    (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int,
    XD_NP varchar(50),
    XD_CL varchar(59),
    XD_ADD1 varchar(75),
    XD_ADD2 varchar(75),
    XD_Coun varchar(30),
    XD_PO varchar(10),
    XD_Zip Varchar(10),
    XD_Sate varchar(30),
    XD_City varchar(50),
    XD_PH1 varchar(20),
    XD_PHT1 varchar(20),
    XD_PH2 varchar(20),
    XD_PHT2 varchar(20),
    XD_PH3 varchar(20),
    XD_PHT3 varchar(20),
    XD_FAX varchar(20),
    XD_NA varchar(100),
    XD_CN varchar(100),
    XD_LocationCode varchar(55),
    XD_ColorCode varchar(10),
    XD_ColorName varchar(50),
    XD_IsDefaultLocation bit --Added By sripada on 12/07/2018 for Is default alternate Location
    ) a WHERE a.XD_IDF = 0

    EXEC sp_xml_RemoveDocument @iDoc



    SET NOCOUNT OFF
    END

    GO

    Monday, July 16, 2018 6:54 AM

All replies

  • User-183374066 posted

    If next time while inserting/Updating if isdefault is 1 then previous all isdefault should 0.

    Place a check before insert / update. If IsDefault is true then run a query to update all the record in the table with IsDefault value 0. 

    If (@IsDefault == 1) {
       Update table set IsDefault = 0
    }
    
    // Your Insert / Update query here
    
    

    Tuesday, July 17, 2018 7:13 AM
  • User1878568433 posted

    Yes. in Insert time it is fine..

    But while updating all is becoming 0 even I select isdefault=1 also

    Please help me while updating what to do. Like one row(IsDefault) will be 1 and Remaining all are 0

    Tuesday, July 17, 2018 8:22 AM
  • User1878568433 posted

    DELETE tbl_In_Client_Alternate_Location FROM tbl_In_Client_Alternate_Location
    LEFT JOIN
    (
    SELECT XD_IDF FROM OPENXML
    (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int
    )
    ) b on tbl_In_Client_Alternate_Location.N_ALOC_ID= b.XD_IDF
    WHERE N_Client_IDF = @N_Client_ID and b.XD_IDF is NULL

    --declare @IsDefaultLocation bit

    --select @IsDefaultLocation=XD_IsDefaultLocation,@Allocation_ID = XD_IDF FROM OPENXML
    -- (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    -- WITH
    -- (
    -- XD_IsDefaultLocation bit,
    -- XD_IDF int
    -- )
    -- IF (@IsDefaultLocation = 1)
    -- Begin
    -- update tbl_In_Client_Alternate_Location set IsDefault=0 where N_Client_IDF=@N_CL_ID
    -- End
    update tbl_In_Client_Alternate_Location
    set
    VC_NPI=TBALTLOC. XD_NP,
    VC_CLIA=TBALTLOC.XD_CL,
    VC_Address_1=TBALTLOC.XD_ADD1,
    VC_Address_2=TBALTLOC.XD_ADD2,
    VC_Country=TBALTLOC.XD_Coun,
    VC_PO_Box=TBALTLOC.XD_PO,
    VC_Zip_Code=TBALTLOC.XD_Zip,
    VC_State=TBALTLOC.XD_Sate,
    VC_City=TBALTLOC.XD_City,
    VC_Phone1=TBALTLOC.XD_PH1,
    VC_Phone_Type1=TBALTLOC.XD_PHT1,
    VC_Phone2=TBALTLOC.XD_PH2,
    VC_Phone_Type2=TBALTLOC.XD_PHT2,
    VC_Phone3=TBALTLOC.XD_PH3,
    VC_Phone_Type3=TBALTLOC.XD_PHT3,
    VC_Fax=TBALTLOC.XD_FAX,
    VC_Name=TBALTLOC.XD_NA,
    VC_Con_Name =TBALTLOC.XD_CN,
    VC_Location_Code=TBALTLOC.XD_LocationCode,
    ColorCode=TBALTLOC.XD_ColorCode,
    ColorName=TBALTLOC.XD_ColorName,
    IsDefault= TBALTLOC.XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    from tbl_In_Client_Alternate_Location
    INNER JOIN
    (
    SELECT XD_IDF,
    XD_NP ,
    XD_CL ,
    XD_ADD1 ,
    XD_ADD2 ,
    XD_Coun ,
    XD_PO ,
    XD_Zip ,
    XD_Sate ,
    XD_City ,
    XD_PH1 ,
    XD_PHT1 ,
    XD_PH2 ,
    XD_PHT2 ,
    XD_PH3 ,
    XD_PHT3 ,
    XD_FAX,
    XD_NA ,
    XD_CN ,
    XD_LocationCode,
    XD_ColorCode,
    XD_ColorName ,
    XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    FROM OPENXML (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int,
    XD_NP varchar(50),
    XD_CL varchar(59),
    XD_ADD1 varchar(75),
    XD_ADD2 varchar(75),
    XD_Coun varchar(30),
    XD_PO varchar(10),
    XD_Zip Varchar(10),
    XD_Sate varchar(30),
    XD_City varchar(50),
    XD_PH1 varchar(20),
    XD_PHT1 varchar(20),
    XD_PH2 varchar(20),
    XD_PHT2 varchar(20),
    XD_PH3 varchar(20),
    XD_PHT3 varchar(20),
    XD_FAX varchar(20),
    XD_NA varchar(100),
    XD_CN varchar(100),
    XD_LocationCode varchar(55),
    XD_ColorCode varchar(10),
    XD_ColorName varchar(50),
    XD_IsDefaultLocation bit --Added By sripada on 12/07/2018 for Is default alternate Location
    )
    )AS TBALTLOC on TBALTLOC.XD_IDF =tbl_In_Client_Alternate_Location.N_ALOC_ID and tbl_In_Client_Alternate_Location.N_Client_IDF = @N_Client_ID


    --select @IsDefaultLocation=XD_IsDefaultLocation,@Allocation_ID = XD_IDF FROM OPENXML
    -- (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    -- WITH
    -- (
    -- XD_IsDefaultLocation bit,
    -- XD_IDF int
    -- )
    -- IF (@IsDefaultLocation = 1)
    -- Begin
    -- update tbl_In_Client_Alternate_Location set IsDefault=0 where N_Client_IDF=@N_CL_ID
    -- End
    INSERT INTO tbl_In_Client_Alternate_Location
    (
    N_Client_IDF,
    VC_NPI,
    VC_CLIA,
    VC_Address_1,
    VC_Address_2,
    VC_Country,
    VC_PO_Box,
    VC_Zip_Code,
    VC_State,
    VC_City,
    VC_Phone1,
    VC_Phone_Type1,
    VC_Phone2,
    VC_Phone_Type2,
    VC_Phone3,
    VC_Phone_Type3,
    VC_Fax,
    VC_Name,
    VC_Con_Name ,
    VC_Location_Code ,
    ColorCode,
    ColorName,
    IsDefault --Added By sripada on 12/07/2018 for Is default alternate Location

    )

    SELECT @N_CL_ID,
    XD_NP,
    XD_CL,
    XD_ADD1,
    XD_ADD2,
    XD_Coun,
    XD_PO,
    XD_Zip,
    XD_Sate,
    XD_City,
    XD_PH1,
    XD_PHT1,
    XD_PH2,
    XD_PHT2,
    XD_PH3,
    XD_PHT3,
    XD_FAX,
    XD_NA,
    XD_CN,
    XD_LocationCode,
    XD_ColorCode,
    XD_ColorName ,
    XD_IsDefaultLocation --Added By sripada on 12/07/2018 for Is default alternate Location
    FROM OPENXML
    (@iDoc, @xmlSchemaClientLocation, @xmlMapClientLocation)
    WITH
    (
    XD_IDF int,
    XD_NP varchar(50),
    XD_CL varchar(59),
    XD_ADD1 varchar(75),
    XD_ADD2 varchar(75),
    XD_Coun varchar(30),
    XD_PO varchar(10),
    XD_Zip Varchar(10),
    XD_Sate varchar(30),
    XD_City varchar(50),
    XD_PH1 varchar(20),
    XD_PHT1 varchar(20),
    XD_PH2 varchar(20),
    XD_PHT2 varchar(20),
    XD_PH3 varchar(20),
    XD_PHT3 varchar(20),
    XD_FAX varchar(20),
    XD_NA varchar(100),
    XD_CN varchar(100),
    XD_LocationCode varchar(55),
    XD_ColorCode varchar(10),
    XD_ColorName varchar(50),
    XD_IsDefaultLocation bit --Added By sripada on 12/07/2018 for Is default alternate Location
    ) a WHERE a.XD_IDF = 0

    Can up please check my code and let me know, How to do.

    Thursday, July 19, 2018 6:42 AM