Strange problem with SQL 2005 server Stored Procedure executing the same SELECT many, many times

Question

Hello,

 I got a strange problem. Let me give you some information.

There is a stored procedure having one select returning the set of data and the return value of 0.

Before the "main" select in the sp, there's also fake select (because of SSIS and codegen tools) looking like this :

IF (1=0)
BEGIN
 CONVERT(INT, NULL) AS Id
 ,CONVERT(VARCHAR(50), NULL) AS someColumn
        ,...
END

There's one last issue out of ordinary - the main select construct uses "TOP" keyword with an integer variable instead of scalar value. Looks like:

SELECT TOP (@top)
...
...
FROM
...

Now to the problem: When the procedure is called from SSMS directly, it runs in few ms. When called from application, it runs a few minutes..We have found out thanks to the SQL Profiler, that the "main" select inside the procedure is being called thousands of times when the procedure is called from the application (we use MS Enterprise library SqlDatabase object, method ExecuteReader; .NET 2.0 approach).

The fact is that our application is calling hundreds of other procedures without exhibiting similiar problem for years. So .. I hope someone has the knowledge we lack..

Answer 1

SOLVED!

Still, I do not get it. It seems that the dynamic SQL (the TOP clause) is the issue. I have removed the dynamic syntax according to this article and it worked (at least so far).

If anyone can clear it for me, the question still stands there.