CODEWITHSUNDEEP
sql-server-2008stored-procedures
A.Goutam
A.Goutam

Reputation: 3494

Incorrect syntax near in stored procedure

I created a stored procedure. I get an error when I want to execute it. The message is

Incorrect syntax near @ErrorMessage.

What is doing mistake in this stored procedure?

ALTER procedure  [dbo].[CWPAYROLL_REVERSE] 
   @TRANID integer,
   @CALLDUE tinyint,
   @SETRDATE tinyint,
   @OUTTRANID integer out,
   @ErrorMessage varchar(4000) out  
As      
begin      
   declare @TRANTYPE char(5);      
   declare @TAXTRANID integer;      
   declare @OUTTAXTRANID integer;      
   declare @UNFINNAME char(5);      
   declare @REVERSENAME char(5);      
   declare @DRIVERID integer;

   BEGIN TRY
   BEGIN TRANSACTION
      SELECT
          @DRIVERID = DRIVERID,
          @TRANTYPE = TRANTYPE,
          @TAXTRANID = TAXTRANID   
      FROM
          PAYROLL 
      WHERE
          TRANID = @TRANID;      

      SELECT
          @UNFINNAME = UNFINNAME,
          @REVERSENAME = REVERSENAME  
      FROM 
          PAYROLLTRANTYPES 
      WHERE 
          TRANTYPE = @TRANTYPE;      

      IF (@UNFINNAME is null) or (@REVERSENAME is null)
      BEGIN 
            --raiserror 23000 'UNFINALIZED OR REVERSE TRAN TYPES ARE BLANK'      
    @ErrorMessage = 'UNFINALIZED OR REVERSE TRAN TYPES ARE BLANK' // Getting error in this line 
            return

      END 
    if(@TAXTRANID is not null)        
        exec CWPAYROLL_REVERSE @TAXTRANID,0,0,@OUTTAXTRANID output ,@ErrorMessage output      
    else      
        set @OUTTAXTRANID=null      

        exec GETNEWRECID2 'PAYROLL','TRANID','PAYROLL',@OUTTRANID  output      
        SELECT @OUTTRANID AS NEWTRANTYPE     
      insert into  PAYROLL(TRANID,TRANTYPE,DRIVERID,TRANDESC,TRANDATE,AMOUNT,PAID,TAXTRANID)      
        select @OUTTRANID,@REVERSENAME,DRIVERID,'REVERSE FOR '+TRANDESC,      
          TRANDATE,-AMOUNT,1,@OUTTAXTRANID from      
           PAYROLL where      
          TRANID = @TRANID;     

    update  PAYROLL set TRANTYPE = @UNFINNAME,PAID = 1 where TRANID = @TRANID;      
    if @CALLDUE = 1        
         exec PAYROLL_SETDUE @DRIVERID,@SETRDATE   
     COMMIT TRANSACTION
   END TRY
   BEGIN CATCH 
        ROLLBACK TRANSACTION
         --SELECT  @ErrorMessage  = ERROR_MESSAGE()  
            RAISERROR  23000  @ErrorMessage  
   END CATCH
END

Upvotes: 0

Views: 449

Answers (2)

Ajay
Ajay

Reputation: 6590

You can not directly call like this

@ErrorMessage = 'UNFINALIZED OR REVERSE TRAN TYPES ARE BLANK'

It should be 

Set @ErrorMessage = 'UNFINALIZED OR REVERSE TRAN TYPES ARE BLANK';

Upvotes: 1

Krish KvR
Krish KvR

Reputation: 1054

SET @ErrorMessage = 'UNFINALIZED OR REVERSE TRAN TYPES ARE BLANK'

Upvotes: 0

Related Questions