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?
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!
0 comments:
Post a Comment