CODEWITHSUNDEEP
sqlsql-server-2005
Ashish
Ashish

Reputation: 21

Stored Procedure returning duplicate results where as firing the sql it runs directly doesn't

We have got a stored procedure in SQL Server 2005 with a complicated bit of single select query. Recently in one environment, we noticed that for a small subset of the results returned by the stored proc, there were duplicate records. When we ran the sql query directly, we got the correct set of records without any duplicates. The stored procedure uses a lot of views and joins (inner join/left join). One theory I have is that somehow the stored procedure is using some cached execution plans as we have modified some views recently, but I don't have enough SQL expertise to be sure of that. Does any one have any idea?

Thanks for your help, Ashish

Upvotes: 2

Views: 934

Answers (2)

MaD
MaD

Reputation: 106

Different results might be caused by different connection settings (e.g. ansi_nulls, arith_abort etc.).

Upvotes: 1

Jon Raynor
Jon Raynor

Reputation: 3892

Run sp_recompile on the stored procedure to clear the procedure cache for that stored procedure.

To clear the entire procedure cache execute

DBCC FREEPROCCACHE

Here's an example of recompiling if you want to put it in a re-usable script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Maintenance - StoredProcedure [Sample].[SampleSearch]    Script Date: 07/28/2011 14:15:15 ******/
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'Sample' AND ROUTINE_NAME = 'Sampleearch'))
BEGIN
    PRINT 'Marking procedure [Sample].[SampleSearch] for recompile'
    EXEC sp_recompile 'Sample.SampleSearch'
    PRINT 'Finished marking procedure [Sample].[SampleSearch] for recompile'
END

GO

However, if the query is returning different results, maybe turn on SQL tracing or debug the call from the code to ensure the same in and out parameters are being used in both cases.

Upvotes: 0

Related Questions