开发者

How can data vary pending how I view them

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 J开发者_高级运维oin bug.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜