Getting different results from Query Analyser than Stored Procedure

Question

Getting different results from Query Analyser than Stored Procedure

KYSC

Old Hand

Points: 392
More actions
March 20, 2006 at 5:21 pm

#112332

I am getting diffrent results when i run a stored procedure than if i run the same query (which is inside the stored proc) in query analyser..
Can anybody explian why is this happen ( if someone needs my query i can send that too... )
Thanks,
KYSC

Viewing 10 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

i recently had a view that gave different column definitions that if i ran the select portion of the view from getting the select via SP_HELPTEXT viewname.

as a result, i simply ran sp_compile viewname to fix it.

try running sp_compile your_proc_name and see if that fixes it;

after that, check to see if your enviornment variables are the same;

it could be things like the SET commands, SET ANSI_NULLS ON , or one of the other SET commands;

you could try posting your proc and see if we can see any identifiable issues.

HTH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

KYSC Old Hand Points: 392 More actions · Answer 2

Thank you for your reply..

But I couldnt find sp_compile in my SQL Server databases..

Where I can find that ?

Thanks,

KYSC

Lowell SSC Guru Points: 323546 More actions · Answer 3

my bad; in the rush to post, it's the wrong procname

sp_recompile is the correct name;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

KYSC Old Hand Points: 392 More actions · Answer 4

Thanks a lot.. for ur help..

But my problem is still there..

Anyway I wrote the query in another way and now it works fine in SP and Query Analyser..

Vladan SSC-Insane Points: 22072 More actions · Answer 5

KYSC,

You posted you solved the problem by rewriting the query, but in case you still are interested in finding out what was the problem... there is no way to tell why this happens unless you post the query and explain what is the difference between SP and QA results. Are different rows returned? Duplicates occurring? Or is the row set the same, but with differences in some columns? Some sample data that produce the different results would be nice, too, if that is possible.

As an example, quite often people asking this question are in fact getting the same result in both ways, they just forget that QA has a setting that limits the number of characters per column (which means long char/varchar columns are truncated in the output).

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 6

Hi ,

Can you send Your Query what you are writing ?

Regards,

Amit Gupta

KYSC Old Hand Points: 392 More actions · Answer 7

Hi This is the query :

SELECT r.rim_identification_no, rmad.rim_manufacturer_name, rsize.rim_size, rstat.rim_status,t.serial_no, tman.tyre_manufacturer_name, tsize.tyre_size, tstat.tyre_status

FROM dbo.LU_TYRE_STATUS tstat INNER JOIN

dbo.TYRE t ON tstat.tyre_status_auto = t.tyre_status_auto INNER JOIN

dbo.LU_TYRE_DESIGN td ON t.tyre_design_auto = td.tyre_design_auto INNER JOIN

dbo.LU_TYRE_PATTERN tp ON td.tyre_pattern_auto = tp.tyre_pattern_auto INNER JOIN

dbo.LU_TYRE_MANUFACTURER tman ON tp.tyre_manufacturer_auto = tman.tyre_manufacturer_auto INNER JOIN

dbo.LU_TYRE_SIZE tsize ON td.tyre_size_auto = tsize.tyre_size_auto FULL OUTER JOIN

dbo.RIM r INNER JOIN

dbo.LU_RIM_STATUS rstat ON r.rim_status_auto = rstat.rim_status_auto INNER JOIN

dbo.LU_RIM_MODEL rmod ON r.rim_model_auto = rmod.rim_model_auto INNER JOIN

dbo.LU_RIM_SIZE rsize ON r.rim_size_auto = rsize.rim_size_auto INNER JOIN

dbo.LU_RIM_MANUFACTURER rmad ON rmod.rim_manufacturer_auto = rmad.rim_manufacturer_auto

ON t.tyre_auto = r.tyre_auto OR r.tyre_auto = NULL

WHERE (rstat.rim_status = @rim_status) OR (tstat.tyre_status_code = @tyre_status_code)

Thanks,

KYSC

SQLBill SSC Guru Points: 51440 More actions · Answer 8

That's the query as run in QA, correct? When you make it a stored procedure, do you add anything?

Are you running the stored procedure from QA or Enterprise Manager or from another source?

In your stored procedure I would add the line SET NOCOUNT ON. SQL Server returns a line showing how many rows are affected. Sometimes that 'messes' up other applications when they run a stored procedure.

Also, it would help if we knew what the QA version returned compared to the stored procedure version.

-SQLBill

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

The part that could produce different results is probably this line:

ON t.tyre_auto = r.tyre_auto OR r.tyre_auto = NULL

You should never use '= NULL' for null comparisons.

It should be changed to

ON t.tyre_auto = r.tyre_auto OR r.tyre_auto IS NULL

/Kennet