CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jose Angel
Jose Angel

Reputation: 65

Conversion failed when converting the nvarchar value to datatype int

I have this two stored procedure that return data:

ALTER proc [dbo].[spBuscarUrl]
@CodigoContrato nvarchar(255),  
@url nvarchar(255) OUTPUT  
AS    

    SET NOCOUNT ON;  
    SELECT @url = url  
    FROM v_compras_y_contrataciones AS cc  
    WHERE CodigoContrato = @CodigoContrato;  
RETURN

When I'm trying to return the value for the second stored procedure, the error says:

ALTER procedure [dbo].[spCaller]
AS BEGIN
DECLARE @URL nvarchar(255);  
EXECUTE spBuscarUrl  
    'MIREX-2017-00001', @url = @URL OUTPUT;  
RETURN convert(nvarchar(255),@URL);  
END

Msg 245, Level 16, State 1, Procedure spCaller, Line 6 Conversion failed when converting the nvarchar value 'https://comunidad.comprasdominicana.gob.do//Public/Tendering/OpportunityDetail/Index?noticeUID=DO1.NTC.2' to data type int.

Upvotes: 0

Views: 4078

Answers (2)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

The problem is this line in spCaller:

RETURN convert(nvarchar(255),@URL);

The RETURN statement can only return INT, not NVARCHAR. If you want to return anything from a Stored Produre that is not an INT you need to either use an output parameter or SELECT.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271131

If you want to return a value, use a function. In this case, though, you can't do that because of the execute. So use an output parameter:

CREATE FUNCTION [dbo].[spCaller] (
    out_url nvarchar(255) output
) AS
BEGIN
    EXECUTE spBuscarUrl 'MIREX-2017-00001', @url = @out_URL OUTPUT;  
END;

Based on your execute statement, you seem to know how to call such a stored procedure.

Upvotes: 0

Related Questions