Hi,

I'm getting [Must declare the variable '@CNT'.] error while executing the
below query. Please help.


CREATE PROCEDURE [SSA_CATEGORY_ADDENTRY]
@TABLENAME VARCHAR(250),
@NEWENTRY VARCHAR(250)
AS
     DECLARE @TMP NVARCHAR(250)
     DECLARE @SQL NVARCHAR(250)
     DECLARE @CNT INT
     IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME=@TABLENAME)
      BEGIN
                SET @SQL = 'SELECT @CNT = COUNT(CT_NAME)  FROM '  +
@TABLENAME + '  WHERE CT_NAME= ' + @NEWENTRY
                EXEC SP_EXECUTESQL @SQL
               IF @CNT = 0
                        BEGIN
                            EXEC ('INSERT INTO ' + @TABLENAME +'(CT_NAME)
VALUES (' + @NEWENTRY + ')')
                            return(0)
                        END
               IF @CNT <> 0
                Return (1)
      END
GO

Microsoft wrote:
Show quoteHide quoteYou're using sp_execute to run a dynamic query, which is essentially
opening a new session.  @CNT doesn't exist in that session context.

The immediate error is because the @CNT variable is out of scope.  You need
to declare parameters in order to pass values to/from dynamically executed
SQL.  However, I see several other issues as well.  The example below uses
EXISTS instead of COUNT and performs the operation as a single INSERT.  This
will prevent duplicate rows data if the proc is executed simultaneously with
the same values.

See http://www.sommarskog.se/dynamic_sql.html for a discussion of dynamic
SQL techniques.  There may be a better way to accomplish this.


CREATE PROCEDURE [SSA_CATEGORY_ADDENTRY]
    @TABLENAME varchar(250),
    @NEWENTRY varchar(250)
AS

DECLARE @SQL nvarchar(4000)
DECLARE @ROWS_INSERTED int

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME=@TABLENAME)
BEGIN
    SET @SQL = N'INSERT INTO ' + QUOTENAME(@TABLENAME) + ' (CT_NAME)
        SELECT @NEWENTRY
        WHERE NOT EXISTS(
        SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WITH (UPDLOCK, HOLDLOCK)
        WHERE CT_NAME = @NEWENTRY)
        SET @ROWS_INSERTED = @@ROWCOUNT'

EXEC sp_executesql
    @SQL,
    N'@NEWENTRY varchar(250), @ROWS_INSERTED int OUTPUT',
    @NEWENTRY = @NEWENTRY,
    @ROWS_INSERTED = @ROWS_INSERTED OUTPUT

    IF @ROWS_INSERTED = 0
        BEGIN
            RETURN 1 --row already exists
        END
    ELSE
        BEGIN
            RETURN 0 --new row inserted
        END
END

RETURN 2 --table not found
GO

Show quoteHide quote

Foreign Key may cause multilple cycles
Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers
How do I filter out unwanted rows by only one different column?
Orders Per Hour Count