Stored Proc recompiles on a Setting a variable?

[bluecjh]

In SQL Server 2000
I have examined a trace on my SP, this shows that
after starting the line:

SET @iNextRowId = (SELECT MIN(id) FROM dbo.t_test)

my sp recomplies, why would this be?

below I give the context:

USE ReportsV4

IF EXISTS(select name from sysobjects where name='p_test')
DROP PROCEDURE p_test

go

CREATE PROCEDURE p_test

AS

SET NOCOUNT ON

TRUNCATE TABLE dbo.t_test
TRUNCATE TABLE dbo.t_test2

DECLARE @nulldate DATETIME,
	@returnvalue INT,
	@bolcountabsences BIT,
	@bolreqrecord BIT,
	@appliedfortargetdate DATETIME,
	@bolactionoutstanding BIT,
	@eventid varchar(40),
	@iCurrentRowId INT,
	@iLastRowId INT,
	@bolbreach TINYINT,
	@dpreviousudate DATETIME,
	@Earliest_Supervision DATETIME,

	@iLoopControl 	INT,
	@iNextRowId	INT,

	@ucount INT,
	@bolappoccurred BIT,
	@bolnewsuper BIT,
	@2nducontactdate DATETIME,

	@v_bolappoccurred BIT,
	@v_bolreqrecord BIT,
	@v_bolnewsuper BIT,
	@v_surname VARCHAR(40),
	@v_forename VARCHAR(40),
	@v_crn VARCHAR(20),
	@v_teamname VARCHAR(250),
	@v_supervisiontype VARCHAR(2),
	@v_commencement DATETIME,
	@v_eventid VARCHAR(32),
	@v_termdate DATETIME,
	@v_ordertype VARCHAR(250),
	@v_apptkept VARCHAR(12),
	@v_action VARCHAR(50),
	@v_contactdate DATETIME,
	@v_contacttype CHAR(12),
	@v_officerid VARCHAR(10),
	@v_offsurname VARCHAR(40),
	@v_offforename VARCHAR(40),
	@v_supervisioncount INT,
	@v_breachcount INT,
	@v_targetdate DATETIME,
	@v_noactionoccurred INT,
	@v_bolbreach TINYINT,
	@v_orderswithbreachcount INT

SET @bolbreach = 0
SET @bolcountabsences = 1
SET @bolappoccurred = 0
SET @bolreqrecord = 0
SET @ucount = 0
SET @bolnewsuper = 0
SET @nulldate = NULL
SET @bolactionoutstanding = 0
SET @Earliest_Supervision = DateAdd(dd,-750,Getdate())

INSERT INTO dbo.t_test

SELECT	
	@bolappoccurred,
	@bolreqrecord,
	@bolnewsuper,
	c.surname,
	c.forename,
	c.crn,
	d_t.description,
	s.commencement_dt,
	s.ref_supervision,
	s.termination_dt,
	l.dec_yesno_apptkept,
	l.action,
	l.contact_dt AS action_date,
	l.dec_contacttype,		
	sp.pocode AS officerid,
	st.surname AS offsurname,
	st.forename AS offforename,
	0,
	0,
	@nulldate,
	0,
	@bolbreach,
	0
	
FROM
	dbo.client c 
	INNER JOIN dbo.supervision s ON c.ref_client = s.ref_client
	INNER JOIN dbo.contactlog l ON l.ref_event = s.ref_supervision AND l.dec_eventtype = 'supervision'
	LEFT JOIN dbo.staffpost sp ON s.ref_staffpost = sp.ref_staffpost
	LEFT JOIN dbo.staff st ON sp.ref_staff = st.ref_staff
	LEFT JOIN dbo.decode_items d_t ON d_t.code1=sp.dec_team AND d_t.class= 'team'

WHERE
	s.commencement_dt > @Earliest_Supervision
	AND s.dec_supervision ='01'
	AND s.isdeleted IS NULL
	AND l.isdeleted IS NULL
	AND c.isdeleted IS NULL
	AND s.transferout_dt IS NULL
	AND 	(
			l.dec_yesno_apptkept = 'u'
		OR	(
			(l.action LIKE '%summ%%appl%' AND l.action NOT LIKE '%void%')
			OR l.action IN ('%warrant applied%','%to contin%','%otc%','%br%%exe%')  --breach exempt ammended
			OR ((l.action IN ('%withdrawn%','%breach%','%gymc%','%wnmc%','%cnmc%'))
				AND (l.action NOT IN ('%tf%','%think first%','%ltr%','%letter%')))
			)
		)
	AND
		(SELECT COUNT(*) 
			FROM 
				dbo.supervision s1
			INNER JOIN dbo.contactlog l1 ON l1.ref_event = s1.ref_supervision AND l1.dec_eventtype = 'supervision' 
			WHERE
				s1.ref_supervision = s.ref_supervision
			AND 	l1.dec_yesno_apptkept = 'u'
		)>1

	ORDER BY 	s.ref_supervision, l.contact_dt

------------------------------------------------------------
SET @iLoopControl = 1

[COLOR=red]SET @iNextRowId = (SELECT MIN(id) FROM dbo.t_test)[/color]

...

thanks for any help

Chris

 

[fredericofonseca]

it would help if you placed this question on the SQL Server forum, not the ANSI SQL, as this has nothing to do with SQL on it's own, but with the way SQL Server deals with Stored Procedures.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com