0

There might be number of questions regarding calling stored procedure from another stored procedure in SQL Server, or passing output value from stored procedure to another. But, I have a problem calling a stored procedure having output value, it always return error converting varchar to int.

Stored procedure #1:

CREATE PROCEDURE [dbo].[SP1]
    @VarIn VARCHAR(MAX),
    @VarOut VARCHAR(MAX) OUTPUT
AS
BEGIN
    SET @VarOut = '20, 21'
END

Stored procedure #2

CREATE PROCEDURE [dbo].[SP2]
    @Var1 VARCHAR(MAX)
AS
BEGIN
    DECLARE @InstNo VARCHAR(MAX)

    EXEC [dbo].[SP1] NULL, @InstNo OUTPUT

    SELECT @InstNo
END

I'm using right click on the stored procedure to execute it:

DECLARE @return_value int

EXEC    @return_value = [dbo].[SP2]
        @Var1 = NULL

SELECT  'Return Value' = @return_value
GO

The error message is

Conversion failed when converting the varchar value '20, 21' to data type int

By the way, I got reference from the accepted answer from this question.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aldr
  • 11
  • 6
  • What does `int` in `DECLARE @return_value int` do? – Salman A Dec 26 '18 at 08:31
  • 3
    With the query that you have posted, you would not and does not get the error message. Please verify your query and post the actual – Squirrel Dec 26 '18 at 08:33
  • Because `@return_value` should not be not int, check the flow: it gets a `varchar(max)` – ZorgoZ Dec 26 '18 at 08:33
  • @SalmanA It is generated automatically when I click "Execute Stored Procedure..." from SQL Server Management Studio. The error still occurs when I remove that. – aldr Dec 26 '18 at 08:34
  • Dont delete it, change type. SSMS might not be prerfect on this... – ZorgoZ Dec 26 '18 at 08:35
  • @Squirrel Only insert and update in the query, I don't think that's necessary. – aldr Dec 26 '18 at 08:36
  • 1
    @Aldriyan not sure what you mean by that. What I am saying is, the query that you have posted here is fine. I have tried it and it does not result in that conversion error message at all – Squirrel Dec 26 '18 at 08:37
  • instead of '@return_value' int use '@return_value' varchar – Kedar Limaye Dec 26 '18 at 08:38
  • @ZorgoZ Do you mean changing it to varchar? As far as I know, SQL Server SP only return integer, so that's why we're using output parameter. But, let me try. – aldr Dec 26 '18 at 08:38
  • I am not getting error with SQL Server Management Studio v17.9.1 – Derviş Kayımbaşıoğlu Dec 26 '18 at 08:48
  • @Squirrel It's strange, isn't it? I also have restarted SSMS, but still the same. Might need to explore more what causing the problem. Anyway, thank you. – aldr Dec 26 '18 at 08:48
  • You might not be posting the exact query that you have in your database. Create a new database and create SP1, SP2 in there and try – Squirrel Dec 26 '18 at 08:49
  • 2
    I am almost very sure that the last statement your stored procedure `SP2` inside your DB is actually not `SELECT @InstNo` but `RETURN @InstNo`. – Squirrel Dec 26 '18 at 08:56
  • in your SP1 can you declare the variable like this @VarIn VARCHAR(MAX) = NULL – JC Borlagdan Dec 26 '18 at 09:01
  • and @Squirrel is also correct that it should be `RETURN @InstNo` because if you do a select, you're trying to pass/return a table. – JC Borlagdan Dec 26 '18 at 09:04
  • @Squirrel no, it's `SELECT` not `RETURN`. I might need to try your suggestion by creating new database. – aldr Dec 26 '18 at 09:14
  • @JCBorlagdan yes, I can. I have tried that and still goes the same. Thanks for your input. The problem seems not the query, but something else, Still trying to figure it out. – aldr Dec 26 '18 at 09:18
  • 1
    You haven't posted a true representation of your issue because that doesn't throw that error. I suggest you run each piece individually - first `SP1` on it's own. Then `SP2` without capturing any return values. – Nick.Mc Dec 26 '18 at 11:12

0 Answers0