Hi, I have a database with several stored procedures. If I run a stored procedure that count a number of rows based on a criteria I don't get the actual result, it seem that it return a cached result. But if I execute the procedure from a website I get the correct result. Is this something that can be configured in SQL Server Manager and why does it happen?
A stored procedure normally would return the same results whether called from a client app or from SSMS, unless there are some parameter differences, or something similar that causes the queries to be different. As far as I know, stored procedures do not cache results between runs.
Open up your code and look at what the differences might be. Or if you are able to post representative code here, for participants might be able to offer suggestions.
Agreed, that "can't happen"
There is a "shortcut" for "Row count in table" which uses a value stored in the Index (instead of doing a COUNT(*) ) and if you Proc is using that method it may not be accurate at all times.
From how you describe your situation it seems unlikely that that code is being used though.
Another possibility is that you have two objects with the same name, but different schema. If you say:
SELECT COUNT(*)
FROM MyTable
then normally that would be the table dbo.MyTable, but if you are logged in as "Fred" and there is a schema called FRED (and table) then that same code would actually perform
SELECT COUNT(*)
FROM FRED.MyTable
Our in-house coding rules require the Schema to be specified in all statements, except where we want the "current schema" to be used and in that circumstance we require that the statement has a comment to that effect.
Also, if you code:
SELECT COUNT(*)
FROM MyTable
SQL has to check IF there is an object called MyTable in the CURRENT schema, and then if not to reference dbo.MyTable instead, which obviously has performance implications [if most/all your tables are in the dbo. schema] so that's another reason why we explicitly include the dbo. schema in statement
I know it sounds very weird. But it does happend... In my stored procedure I don't have the dbo.tablename but just the tablename. If I run this procedure directly from SSM I get the wrong result. But if I call the very same procedure from a windows application I get correct result. And if I after its been called from the windows client go back to SSM and run the same procedure again it show the same result as in the windows application.
Well ... you could change it to be dbo.tablename and see if that fixes it - if it does then look for the additional schema that were spoiling your day!
Suggest you post the code in case anyone here can see anything that could cause variable behaviour. Change the Table / Column names etc. if they are confidential.