How can data vary pending how I view them

Question

I have an odd case where when I look at the data through my SQL scripts I have one value, but if I look at the data directly SELECT * FROM table I get another value.

My first thought was parameter sniffing, but that didn't fix the issue. I'm not doing anything with the value at hand, except getting it with a stored procedure.

Example of the stored procedure.

CREATE PROCEDURE example
(
@iRefProjectID int
)
AS
-- Prevent Parameter sniffing
DECLARE @projectID int
SET @projectID = @iRefProjectID

SELECT iEntryType
FROM table
WHERE iEntryType IN (1,5,6)
AND iProjectID = @projectID
RETURN

GO

Now one of the rows so extracted contains a '2', which when I look at it through the SP it is a '1'. It shouldn't have been picked at all being 2 != 1 || 5 || 6. Suddenly the 2 becomes a 1 and then "1" == 1.

Where should I look to kill this bug.

The rows in question
SELECT * FROM table
3264427 2003-11-25 00:00:00.000 **2** Udligning til afregning F83907 100625.00

Exec SP
3264427 2003-11-25 00:00:00.000 -100625.00 Udligning til afregning F83907 **1**

Ahh .. found something. This rather looks like a Join bug.

Answer 1

There is no such bug.

You probably have 2 tables with same name in different schemas. Example: dbo.table and [DOMAIN\User].table

Best practice is always qualify objects to avoid incorrect schema resolution.

There are other options such as:

• different databases
• different servers
• table is actually an unrefreshed view
• dirty read (as per Yves M. comment) because you have changed isolation level