June 25, 2021 at 4:28 pm
select @MaxRows = count(distinct AT.AccountId)
from ATable AT
inner join SomeTable A on A.AccountID = AT.AccountID
where AT.EmailAddress = @EmailAddress
and
(
(@CEID = -1 or CEID = @CEID) and
(@CSCEID = -1 or CSCEID = @CSCEID)
) and (a.Commission = @Commission or @Commission_temp = 1) and (@AccountTypeID = -1 or AccountTypeID = @AccountTypeID)
I'm sending across the @EmailAddress after correct encryption but the query does not work during execution in PROCEDURE.
Here EmailAddress in the DB are AES encrypted at column level.
Email in the DB will be as below:
0x01E28700F332D2B2185DAE188DC400E00086EB00A5CE57FB6A9C59EE51AC994CA75EBA88198DB7064AA1B837EEC35687466DE8F53C8D590E53148C4B7EDC0D61F3CDBDDE75AC9BC773316F5D54CF01D802
June 26, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 28, 2021 at 12:28 pm
Not seeing your structure, it's hard to know what the issue might be. I'd break it down. Take the query down to it's component parts & rebuild them, one step at a time, ensuring that you're getting data at each point. Then, you can tell what is breaking as you add stuff back in.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2021 at 8:49 pm
What is the data type of the parameter @EmailAddress? How are you passing the encrypted email address from the application to SQL Server? If you have different data types at either end then implicit conversion of the data would make it not match and you would not get any data returned.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2021 at 8:47 am
Do what Grant said!
i.e. start with
select @MaxRows = count(distinct AT.AccountId) from ATable AT
Does that work?
Next, do
select @MaxRows = count(distinct AT.AccountId) from ATable AT
inner join SomeTable A on A.AccountID = AT.AccountID
where AT.EmailAddress = @EmailAddress
Does that work?
Continue until you know which part breaks it.
June 29, 2021 at 10:29 am
What error are you are getting?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply