SET NOCOUNT ON, but SP does not return records

[Danny]

I am working with Office XP, SQL Server 2000 with the latest service packs.

I have a sp that uses a temporary table to gather records to be displayed in
a result table.

The ‘SET NOCOUNT ON’ statement is at the top of the procedure and it works
perfectly in Query Analyzer.

Can anyone crack this nut?


Code…

CREATE PROCEDURE spQualityCheck
(@JobID int, @ProductionOnly bit = 0) AS

SET NOCOUNT ON

/*
___________________________________________________________________


COMMENT: Runs a check on all of the specifications for an order.

Has the option of only testing specifications that are
relevant to the operator.

1.) Checks if there are rolls to look at
2.) Run a check if a specification has been tested
3.) Checks the data against the specification.


RETURNS: Table of rolls out of spec and tests not performed yet.
___________________________________________________________________

*/
DECLARE @SpecDescription nvarchar(255)
DECLARE @TableName nvarchar(50)
DECLARE @FieldName nvarchar(50)
DECLARE @Spec nvarchar(255)
DECLARE @SQL nvarchar(1000)
DECLARE @JobString as nvarchar(15)

--Create a temporary table to hold the results of the spec checks.

CREATE TABLE #Temp (
Roll int,
[Spec Name] nvarchar(255),
Spec nvarchar(255),
Measurement nvarchar(10))


--Check if there are rolls to test
IF NOT EXISTS(SELECT * FROM tblRolls
WHERE (JobId = @JobID) AND
(RollStatus = 'G') AND
(RollDisposition = 'S' OR RollDisposition = 'H'))
GOTO BAILOUT

--Loop through each of the specifications

SET @JobString = CONVERT(nvarchar(15), @JobID)
DECLARE CSR CURSOR LOCAL

FOR

SELECT SpecDescription, TableName, FieldName, Spec
FROM tblJobSpecifications
WHERE JobID = @JobID AND
(AlertInProduction = 1 OR AlertInProduction = @ProductionOnly)

OPEN CSR
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName, @FieldName, @Spec
Print 'FETCH START'

WHILE @@FETCH_STATUS = 0
BEGIN
--Make Sure the test has been made
SET @SQL = 'INSERT INTO #Temp (Roll, [Spec Name], Spec, Measurement)
SELECT ''1'' AS Roll, ''' +
@SpecDescription + ''' AS [Spec Name], ''' +
@Spec + ''' AS Spec,
''No Data'' AS Measurement
WHERE(NOT EXISTS (SELECT * FROM ' +
@TableName + ' WHERE JobID = ' +
@JobString + ' AND
RollStatus = ''G'' AND
(RollDisposition = ''S'' OR RollDisposition = ''H'')))'
PRINT @SQL
EXEC(@SQL)
IF @@ROWCOUNT = 0 --There are tests, make the spec check
BEGIN
SET @SQL = 'INSERT INTO #Temp SELECT RollNumber as Roll, ''' +
@SpecDescription + ''' as [Spec Name], ''' +
@Spec + ''' as Spec, Left(CAST([' +
@FieldName + '] AS nvarchar), 10) as Measurement FROM ' +
@TableName + ' WHERE JobID = ' +
@JobString + ' AND NOT ([' +
@FieldName + '] ' +
@Spec + ') AND RollStatus = ''G'' AND
(RollDisposition = ''S'' OR RollDisposition = ''H'')'
PRINT @SQL
EXEC (@SQL)
END
--Move to the next record
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName, @FieldName, @Spec
END

CLOSE CSR
DEALLOCATE CSR

BAILOUT:

SELECT * FROM #Temp
GO