Return string value from stored procedure not working

Question

I have created a stored proc that should return a string value based on which action was taken. The queries is all working fine, except I'm getting an error on the @return_value ("Converting varchar value to int)... I tried casting both values withing the query, but it's not working...

The error I'm receiving:

    Conversion failed when converting the varchar value 'Request ID: 454 captured on 2017-04-25 10:16:07' to data type int.

This is my sql code of what I did:

    USE [VehicleManagement]

    GO
    /****** Object:  StoredProcedure [dbo].[spSaveContractsCaptured]    
    Script Date: 2017/04/23 8:56:10 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_SaveContractsCaptured]
    @VehicleServiceRequestID int,
    @InvoiceNo nvarchar(50),
    @Amount decimal(18, 2),
    @OdometerReading float,
    @ExpiryDate datetime,
    @ServiceDate datetime,
    @CapturedBy nvarchar(50),
    @CapturedDate datetime,
    @ContractStartDate datetime,
    @PeriodExpiry nvarchar(50),
    @returnVal nvarchar(255) output

    AS
  BEGIN
  SET NOCOUNT ON

  IF NOT EXISTS (SELECT ID FROM [VehicleManagement].[dbo].[VehicleService_Captured]
  WHERE VehicleServiceRequestID = @VehicleServiceRequestID )  
  BEGIN
        INSERT INTO [VehicleService_Captured] (VehicleServiceRequestID, InvoiceNo,
        Amount, OdometerReading, ExpiryDate, ServiceDate, CapturedBy, CapturedDate, ContractStartDate, PeriodExpiry)
        VALUES (@VehicleServiceRequestID, @InvoiceNo, @Amount, @OdometerReading, @ExpiryDate, @ServiceDate, 
        @CapturedBy, @CapturedDate, @ContractStartDate, @PeriodExpiry) 

        set @returnVal = SCOPE_IDENTITY()
        RETURN CAST(@returnVal AS VARCHAR(255))
    end
else
   SET @returnVal = (SELECT 'Request ID: ' + convert(varchar,ID) +  ' captured on ' + convert(varchar,[CapturedDate],120)  FROM [VehicleManagement].[dbo].[VehicleService_Captured]
    WHERE VehicleServiceRequestID = @VehicleServiceRequestID)

   RETURN CAST(@returnVal AS nvarchar(255))
 END

Is there something I am missing or what am I doing wrong?

Answer 1

As Triv pointed in the right direction, you should just set the value of the output variable and remove the RETURN

USE [VehicleManagement]

    GO
    /****** Object:  StoredProcedure [dbo].[spSaveContractsCaptured]    
    Script Date: 2017/04/23 8:56:10 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_SaveContractsCaptured]
    @VehicleServiceRequestID int,
    @InvoiceNo nvarchar(50),
    @Amount decimal(18, 2),
    @OdometerReading float,
    @ExpiryDate datetime,
    @ServiceDate datetime,
    @CapturedBy nvarchar(50),
    @CapturedDate datetime,
    @ContractStartDate datetime,
    @PeriodExpiry nvarchar(50),
    @returnVal nvarchar(255) output

    AS
  BEGIN
  SET NOCOUNT ON

  IF NOT EXISTS (SELECT ID FROM [VehicleManagement].[dbo].[VehicleService_Captured]
  WHERE VehicleServiceRequestID = @VehicleServiceRequestID )  
  BEGIN
        INSERT INTO [VehicleService_Captured] (VehicleServiceRequestID, InvoiceNo,
        Amount, OdometerReading, ExpiryDate, ServiceDate, CapturedBy, CapturedDate, ContractStartDate, PeriodExpiry)
        VALUES (@VehicleServiceRequestID, @InvoiceNo, @Amount, @OdometerReading, @ExpiryDate, @ServiceDate, 
        @CapturedBy, @CapturedDate, @ContractStartDate, @PeriodExpiry) 

        set @returnVal = SCOPE_IDENTITY()
    END
ELSE
   SET @returnVal = (SELECT 'Request ID: ' + convert(varchar,ID) +  ' captured on ' + convert(varchar,[CapturedDate],120)  FROM [VehicleManagement].[dbo].[VehicleService_Captured]
    WHERE VehicleServiceRequestID = @VehicleServiceRequestID)

 END

And when you call the procedure you should declare a variable and use it with "out" like:

declare @message nvarchar(255)
exec [dbo].[sp_SaveContractsCaptured] //list parameteres, @returnval = @message out

Also be sure that 255 is enough for your message. Hope it helps, cheers!