Same SELECT Stmt yielding DIFFERENT results

Question

Same SELECT Stmt yielding DIFFERENT results

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mymail.default (4/9/2012)
Thank you GSquared. I think you have nailed it. Here is how I have:
The stored proc I created is in a different schema:
create proc Schema1.Test
as
select CodeValue from TableA whereID = 1334802
1. Exec Schema1.Test
2. select CodeValue from TableA whereID = 1334802
The above 2 stmts return different values.
However, if I recreate the stored proc as Test (under dbo), I get identical results.
My question still though is, what difference is cauisng the different results.
Thanks again.

Different data in different tables. It looks like you have a table Schema1.Test and dbo.Test. The stored procedure is reading from Schema1.Test and the direct query is reading from dbo.Table since you did not specify a schema in the query itself.

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

You have TableA in both schemas. When you execute your proc with a specific schema it will first try to find a table with that name under the schema of the proc. If it does not find it there it will look in the dbo schema. Make sense?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Looks like Lynn was typing with his uber fast response keys again while I was typing. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Sean Lange (4/9/2012)
Looks like Lynn was typing with his uber fast response keys again while I was typing. 😀

Except I got the table name wrong. :w00t:

mymail.default SSCommitted Points: 1752 More actions · Answer 5

Thanks Lynn, Sean and GSquared.

(Yes, there is another table in both the schemas with the same name(surprisingly) with different data.)

GSquared SSC Guru Points: 260824 More actions · Answer 6

In queries, procs, etc., I make sure to ALWAYS use schema names for objects, unless I specifically want the query to pull different data for different users. Speeds up execution by a small amount, too, besides just making sure you get the data you actually expect.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mymail.default SSCommitted Points: 1752 More actions · Answer 7

Thanks Gsquared.

Anyway to cause the Schema1 to access objects in the dbo schema as default.

Ex:

--Start

create proc Schema1.Test

as

Select * from tblA

Select * from Schema1.TblA

--End

So when we execute the Schema1.Test proc, we get data from Schema1.tblA, even if there is another table dbo.tblA. Correct?

This is so because the stored proc is running under the Schema1 scope, and it will look for objects in the Schema1 first.

My question again is, is there no way to cause the above proc to return data in dbo.tblA from the first query without specifying the dbo prefix -- Like cuase the Schema1 to look for the dbo schema first?

Thanks.

GSquared SSC Guru Points: 260824 More actions · Answer 8

The only way to do that is to use "dbo.TblA", instead of just "TblA".

Note that doing so actually makes the proc run faster, and allows more concurrent users to run it more efficiently, since it doesn't have to lock the proc while it figures out what schema the table is in.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mymail.default SSCommitted Points: 1752 More actions · Answer 9

mymail.default

SSCommitted

Points: 1752

April 12, 2012 at 10:22 am

#1472743

ok, thanks.