This problem is weird. The stored procedure in QA and my local are identical. But the “count(*)” returns “null” on QA which caused exception, while it returns 0 in my local.
I couldn’t figure out why.
I have tried to change from “SELECT @temp = COUNT (*)” to “SELECT @temp = COUNT (FieldName)” in the stored procedure and that change fixed the problem. Later I changed the stored procedure back to use “SELECT @temp = COUNT (*)”, but now it returns 0 instead of NULL.
I could change all contact search stored procedures to use “SELECT @temp = COUNT (Field)” as a workaround to fix the problems in QA and local. But I don’t know why the same stored procedure behave differently in different Sql servers.
I didn’t find anything like that on Google search. Have you guys ever seen this behavior before?
Any help highly appreciable.