How would or can it be done where I use a select drop down but the value currently saved in my table is no longer in the SQL select list. Now why you may ask is missing. This would because that option is not longer available being it had an active flag on the SQL pulling the list. So being its not active it will not show anymore to be selected. So my issue is that because its not in the new select list it does not show as the current value. The drop down is blank. I would like to be able to show the current value not matter if it’s in the currently select list. Also in this case the select drop down list is a multiple check box select list delimited by comma.
I found a solution not sure if this is the best way but is works.
I created a union on the original value so even if its not in the new list is still will show in the drop down. I also have it splitting the vales as the field hold more than 1 value so I had to split it. Here is how I did both. Hope is help someone.
Select list SQL
select PharmMedCode, MedCodeDesc from PharmacyMedCodes pc
inner join Pharmacies p on pc.PharmacyID=p.PharmacyID
where p.Active=1 and pc.Active=1 and p.PharmCode=’{PharmacyCode}’
union
select PharmMedCode, MedCodeDesc from PharmacyMedCodes pc
inner join Pharmacies p on pc.PharmacyID=p.PharmacyID
WHERE p.PharmCode=’{PharmacyCode}’
AND PharmMedCode IN (
SELECT s.item
FROM leads l
CROSS APPLY dbo.SplitStrings(l.preselectMedicineCode,’,’) s
WHERE l.LeadID={LeadID})
Here is the split string SQL function - it uses XML to split the string. SQL 2016 has this built in but I’m using 2012 so had to create one. I found it here https://sqlperformance.com/2012/07/t-sql-queries/split-strings
CREATE FUNCTION [dbo].[SplitStrings]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value(’(./text())[1]’, ‘nvarchar(4000)’)
FROM
(
SELECT x = CONVERT(XML, ‘<i>’
+ REPLACE(@List, @Delimiter, ‘</i><i>’)
+ ‘</i>’).query(’.’)
) AS a CROSS APPLY x.nodes(‘i’) AS y(i)
);