SP: NOCOUNT ON, but still no records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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))
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

WHILE @@FETCH_STATUS = 0
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)
--Move to the next record
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName,
@FieldName, @Spec
END

CLOSE CSR
DEALLOCATE CSR

BAILOUT:

SELECT * FROM #Temp
GO
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess is that the SET NOCOUNT ON is not the problem. The problem is
probably the final SQL statement that you create in the WHILE loop.

This part is puzzling:

+ ' AND NOT ([' +
@FieldName + '] ' +
@Spec + ') AND RollStatus = ''G'' AND

This would parse out to:

... AND NOT ([fieldname] + <spec>) AND RollStatus ...

Do you mean to have a comparison operator between [fieldname] and
<spec>? Something like the equal sign? E.g.:

([fieldname] = <spec>)

If you mean to use the equal sign you'd not need the "NOT" in front of
the expression, you would use the not equal sign "<>" instead:

... AND ([fieldname] <> <spec>) AND RollStatus ...

==
Note: it appears that 8 of your posts have successfully been posted.
Those w/ times of 09:33 to 09:49.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYEv7YechKqOuFEgEQJyJACcD0g5jNkLic5dyICyFZvNowfL3TgAni0V
RDnfIzDKCjBBY8nbuQ8QUs2i
=Z33U
-----END PGP SIGNATURE-----

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

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))
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

WHILE @@FETCH_STATUS = 0
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)
--Move to the next record
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName,
@FieldName, @Spec
END

CLOSE CSR
DEALLOCATE CSR

BAILOUT:

SELECT * FROM #Temp
GO
Click to expand...
 
I put in some comments below.

I was able to get the sp to work by removing all of the PRINT statements I
was using to debug.

Thanks for your help.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess is that the SET NOCOUNT ON is not the problem. The problem is
probably the final SQL statement that you create in the WHILE loop.

This part is puzzling:

+ ' AND NOT ([' +
@FieldName + '] ' +
@Spec + ') AND RollStatus = ''G'' AND

This would parse out to:

... AND NOT ([fieldname] + <spec>) AND RollStatus ...
Click to expand...

The @FieldName is a field in a separate view. The string parses out to...
.... AND NOT (BasisWt > 25.2) AND RollStatus ...
or whatever depending on the view I get the field from.
Do you mean to have a comparison operator between [fieldname] and
<spec>? Something like the equal sign? E.g.:

([fieldname] = <spec>)

If you mean to use the equal sign you'd not need the "NOT" in front of
the expression, you would use the not equal sign "<>" instead:
Click to expand...
... AND ([fieldname] <> <spec>) AND RollStatus ...
Click to expand...

Right! But it works out better for the guy who is creating the text
specification strings. That way he can build the string to say what is in
spec rather than what is out of spec. It makes it much easier to read (and
write) from their point of view.

==
Note: it appears that 8 of your posts have successfully been posted.
Those w/ times of 09:33 to 09:49.
Click to expand...

Yeah, I was getting error messages when I posted. I should get on better
terms with the refresh button.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYEv7YechKqOuFEgEQJyJACcD0g5jNkLic5dyICyFZvNowfL3TgAni0V
RDnfIzDKCjBBY8nbuQ8QUs2i
=Z33U
-----END PGP SIGNATURE-----

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

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))
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

WHILE @@FETCH_STATUS = 0
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)
--Move to the next record
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName,
@FieldName, @Spec
END

CLOSE CSR
DEALLOCATE CSR

BAILOUT:

SELECT * FROM #Temp
GO
Click to expand...
Click to expand...
 
There must have been some confusion beetween the PRINT statement and the
select from a temporary table.

Temporaray table will always cause you trouble because they are stored on a
different database (the tempdb database) and, as such, are not always
accessible from your connection; even if they are accessible from the inside
of your stored procedures.

S. L.

Danny said:
I put in some comments below.

I was able to get the sp to work by removing all of the PRINT statements I
was using to debug.

Thanks for your help.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess is that the SET NOCOUNT ON is not the problem. The problem is
probably the final SQL statement that you create in the WHILE loop.

This part is puzzling:

+ ' AND NOT ([' +
@FieldName + '] ' +
@Spec + ') AND RollStatus = ''G'' AND

This would parse out to:

... AND NOT ([fieldname] + <spec>) AND RollStatus ...
Click to expand...

The @FieldName is a field in a separate view. The string parses out to...
... AND NOT (BasisWt > 25.2) AND RollStatus ...
or whatever depending on the view I get the field from.
Do you mean to have a comparison operator between [fieldname] and
<spec>? Something like the equal sign? E.g.:

([fieldname] = <spec>)

If you mean to use the equal sign you'd not need the "NOT" in front of
the expression, you would use the not equal sign "<>" instead:
Click to expand...
... AND ([fieldname] <> <spec>) AND RollStatus ...
Click to expand...

Right! But it works out better for the guy who is creating the text
specification strings. That way he can build the string to say what is in
spec rather than what is out of spec. It makes it much easier to read (and
write) from their point of view.

==
Note: it appears that 8 of your posts have successfully been posted.
Those w/ times of 09:33 to 09:49.
Click to expand...

Yeah, I was getting error messages when I posted. I should get on better
terms with the refresh button.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYEv7YechKqOuFEgEQJyJACcD0g5jNkLic5dyICyFZvNowfL3TgAni0V
RDnfIzDKCjBBY8nbuQ8QUs2i
=Z33U
-----END PGP SIGNATURE-----

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

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))
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

WHILE @@FETCH_STATUS = 0
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)
--Move to the next record
FETCH NEXT FROM CSR INTO @SpecDescription, @TableName,
@FieldName, @Spec
END

CLOSE CSR
DEALLOCATE CSR

BAILOUT:

SELECT * FROM #Temp
GO
Click to expand...
Click to expand...
Click to expand...
 
Back
Top