2

I'm trying to store the NOCOUNT status so that I can return it to its original state at the end of my procedure, but all it does is give me an Incorrect syntax near 'NOCOUNT'. error.

What am I doing wrong?

IF @@OPTIONS & 512 <> 0     /* check original state of NOCOUNT */
      PRINT N'This user has SET NOCOUNT turned ON.';
  ELSE
      PRINT N'This user has SET NOCOUNT turned OFF.';

DECLARE @NCStat bit
    SET @NCStat = ( @@OPTIONS & 512 )   /* sets @NCStat to original state of NOCOUNT */

SET NOCOUNT ON ;

IF @@OPTIONS & 512 <> 0     /* verify state of NOCOUNT */
      PRINT N'This user has SET NOCOUNT turned ON.';
  ELSE
      PRINT N'This user has SET NOCOUNT turned OFF.';

PRINT N'NCStat = ' + cast(@NCStat as nvarchar) ;        /* verify value of @NCStat */

/* line 23 */  SET NOCOUNT  @NCStat ;       /* return NOCOUNT to original state */

IF @@OPTIONS & 512 <> 0     /* verify state of NOCOUNT */
      PRINT N'This user has SET NOCOUNT turned ON.';
  ELSE
      PRINT N'This user has SET NOCOUNT turned OFF.';
GO

If line 23 is remarked out, all other lines work fine, but line 23 gives the above error.

chue x
  • 18,573
  • 7
  • 56
  • 70
Deina Underhill
  • 557
  • 1
  • 9
  • 23

1 Answers1

3

You cannot use a variable to set NOCOUNT:

/* line 23 */  SET NOCOUNT  @NCStat ; /* not legal syntax */

I would do this instead:

/* line 23 */
if @NCStat = 1
    SET NOCOUNT ON
else
    SET NOCOUNT OFF
chue x
  • 18,573
  • 7
  • 56
  • 70