Search 'Email address' in where clause in SQL

How to search multiple email address in below SQL query.....

------------------
Declare @EmailId varchar (max)
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

Declare @String4 varchar (Max)

Set @String4 ='select * from EmailSummary ES
where ES.'+@EmailIdWhere+'
----------------------------------------------------------------------------------
Payal sathavaraWindows Developer Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try like this -

Declare @AccountMasterId varchar (max) = ''
set @AccountMasterId='495,12110'

Declare @EmailId varchar (max) =''
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

DECLARE @y AS XML=''
SET @y = CAST('<A>'+ REPLACE(@AccountMasterId,',','</A><A>')+ '</A>' AS XML)

SELECT u.* FROM 
(
	SELECT * FROM #EmailSummary ES 
	INNER JOIN 
	(
		SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
	)p ON p.Value = ES.EMailAddressesTo  
)u
LEFT OUTER JOIN 
(
	SELECT  * FROM AccountMaster A
	INNER JOIN 
	(
		SELECT t.value('.', 'VARCHAR(200)') Value FROM @y.nodes('/A') AS x(t)
	)o 
)A ON A.EMail = u.EMailAddressesTo

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Show columns from your table EmailSummary ?
What column has email ids?
0
 
Payal sathavaraWindows Developer Author Commented:
Declare @EmailId varchar (max)
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

Declare @EmailIdWhere varchar (max)
Set @EmailIdWhere=Case when @EmailId='' then 'EMailAddressesTo<>''''' else 'EMailAddressesTo IN ('''+@EmailId+''')' end

Declare @String4 varchar (Max)

Set @String4 ='select * from EmailSummary ES
where ES.'+@EmailIdWhere+'
0
 
Pawan KumarDatabase ExpertCommented:
You need to try like below - Split and then join , IN clause will not work here.

--
Declare @EmailId varchar (max) =''
set @EmailId='anuradha.dutta@xyz.com,HEENA.SANOTRA@xyz.COM'

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

SELECT * FROM EmailSummary ES
INNER JOIN 
(
	SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
)p ON p.Value = ES.EMailAddressesTo

--

Open in new window

0
 
Payal sathavaraWindows Developer Author Commented:
Dear Sir,
I have also search with accountID as well as Email then ?

Declare @AccountMasterId varchar (max)
set @AccountMasterId='495,12110'

Declare @AccountMasterIdsWhere varchar (max)
Set @AccountMasterIdsWhere=Case when @AccountMasterId='0' then 'AccountMasterId<>-1111' else 'AccountMasterId in ('+@AccountMasterId+')' end

DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@EmailId,',','</A><A>')+ '</A>' AS XML)

      SELECT * FROM #EmailSummary ES LEFT OUTER JOIN AccountMaster A ON A.EMail = ES.EMailAddressesTo
                  INNER JOIN
                  (
                        SELECT t.value('.', 'VARCHAR(200)') Value FROM @x.nodes('/A') AS x(t)
                  )p ON p.Value = ES.EMailAddressesTo      


I  have join account master and search with AccountMasterID....How to set in Query ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.