As I wrote in the comment the answer of your first question is:
If I understand your PRC well, in case of @IP = '' it should returns a wieder set of members. I think it is possible that the Username, Email or Country is longer in the member table then the @Temp table’s definition. Match the column lenghts of @Temp wit table member.
Second answer for your question:
In case @IP=’some value’, it takes 0 secs and in case @IP=”, it takes
1:50 min. What’s wrong here?
I think you should cut your prc to two part. Run both query (with @IP = ” and with=”somevalue”) and check the execution plans.
If you see, that it is working well with ‘somevalue’ and there is no need of an INDEX, I think your query is a victim of parameter sniffing, which generally means that your execution plan is optimized to retrieve all the possibilites and it is not optimal to make a more selective search.
Probably it was optimized for a full table scan as it described in the article what I linked.
2
solved Stored procedure takes too much time in if statement when i pass @ip=” and takes 0 sec with some value to @IP