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
