D
Danny
I couldn't find a solution on the net... Please help.
I have an ADP Project with SQL 2000.
Service packs are installed.
The following sp works nicely in the Query Analyzer, but get the message..
"The stored procedure executed succesfully but did not not return records"
The SET NOCOUNT ON statement is at the beginning of the procedure.
The procedure uses a temporary table #Temp and some dynamic SQL statements
to fill it.
The code is as follows...
__________________________________________________________________
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
___________________________________________________________
I have an ADP Project with SQL 2000.
Service packs are installed.
The following sp works nicely in the Query Analyzer, but get the message..
"The stored procedure executed succesfully but did not not return records"
The SET NOCOUNT ON statement is at the beginning of the procedure.
The procedure uses a temporary table #Temp and some dynamic SQL statements
to fill it.
The code is as follows...
__________________________________________________________________
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
___________________________________________________________