Post reply

SQL Serer 2012 gives error: "Must declare the scalar variable" even when variable is declared

  • December 18, 2014 at 12:50 am

    #293589

    I have below script which runs properly on SQL Server 2005/2008 but gives problem on SQL Server 2012

    ----------------------

    CREATE PROC pGetLastKeyValue (@table_name CHAR(50), @CadCatId int OUTPUT )

    AS

    set nocount on

    DECLARE @holder int

    DECLARE @errno int

    DECLARE @errmsg varchar(255)

    BEGIN

    SELECT @holder = 0 ******* ERROR OCCURS HERE ********

    UPDATE LastKeyUsed

    WITH (TABLOCKX)

    SET @holder = LastKey = (LastKey + 1)

    WHERE LTRIM(RTRIM(TableName)) = LTRIM(RTRIM(@table_name))

    IF @@ERROR <> 0

    BEGIN

    select @errno = 32001,

    @errmsg = '1222 - Failed getting the nextvalue'

    raiserror @errno,10,1, @errmsg

    RETURN

    END

    SELECT @CadCatId = @holder

    RETURN

    END

    ---------------------------------------

    @holder is seen to be declared but still gives error as - Must declare the scalar variable "@holder"

  • December 18, 2014 at 2:47 am

    #1765369

    It looks like you have to add parentheses to your RAISERROR command:

    raiserror( @errno,10,1, @errmsg)

    At least, in my 2012 and 2014 instances it worked.

    -- Gianluca Sartori

  • December 18, 2014 at 3:06 am

    #1765374

    Issue is resolved... in a bit strange way.

    Problem seemed to be due to permission issue. I was executing a Setup by double clicking on its EXE. This script was coming in picture during installation.

    When I executed same Setup under local Administrator user (by right clicking and selecting 'Run As Administraor') it worked properly without any error.

    Don' know how permissions affected this behavior

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply