ExecuteStoreCommand can't return string with SqlParameter output

Question

Something really weird today.

When I try to call a stored procedure in SQL Server with the objectContext from Entity Framework, using the function ExecuteStoreCommand, it always tries to convert to 'string' value to 'int' .. I don't know why.

Even if I set the output as an NvarChar type.

My exception is

Failed converting the varchar value 'text' to int data type

Here is my C# code:

public virtual string GetCardCodeLinkedToAttestation(string atBuisnessCode)
{
    // Input param - String
    SqlParameter atBuisnessCodeParameter = new SqlParameter("atBuisnessCode", atBuisnessCode);

    // The output param - String
    SqlParameter retval = new SqlParameter("retval", SqlDbType.NVarChar,15);
    retval.Direction = ParameterDirection.Output;

    ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand("exec @retval = sp_GetCardCodeLinkedToAttestation @atBuisnessCode", atBuisnessCodeParameter, retval);

    return (string)retval.Value;
}

Here is my stored procedure in SQL Server (I explicitly return 'test' for be sure is a string ):

ALTER PROCEDURE [dbo].[sp_GetCardCodeLinkedToAttestation]
    @atBuisnessCode nvarchar(9)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @retval varchar(15);
    set @retval= '';

    -- Insert statements for procedure here
    SET @retval = (SELECT TOP 1 [CardCode] 
                   FROM [SC-SAP01].[SWC_PROD].[dbo].[OCPR]  
                   WHERE [U_Attestation] = @atBuisnessCode)

    RETURN 'test';
END
GO

Exception :

Failed converting the varchar value 'text' to int data type

I tried to set the value without return it, and declare the output in my stored procedure, but the value returned is always NULL.

The return type for stored procedures is always int. If you want a string, use an output parameter or place it in a result set - don't use return. Also, you should avoid using sp_ as a prefix when naming your stored procedures - it's reserved for Microsoft's System Procedures. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 25, 2016 at 9:22

Mehdi Bugnard · Accepted Answer · 2016-07-25 10:12:31Z

2

Here you need to use Output as return will always return int datatype.

By default, the successful execution of a stored procedure will return 0

Please try below code:

@atBuisnessCode nvarchar(9),
@retval varchar(15) OUTPUT 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @retval varchar(15);
    set @retval= '';

    -- Insert statements for procedure here
    set @retval= (SELECT TOP 1 [CardCode] FROM [SC-SAP01].[SWC_PROD].[dbo].[OCPR]  WHERE [U_Attestation] = @atBuisnessCode)

    --return 'test';
END
GO

edited Jul 25, 2016 at 10:12

Mehdi Bugnard

3,9875 gold badges51 silver badges89 bronze badges

answered Jul 25, 2016 at 9:25

Neel

11.7k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mehdi Bugnard Over a year ago

The return i'm waiting is a string value like (R_123332) . As i can see, is always return a int ? Even if i declare an output as varchar ?. With your solution, I always get the return value [0]..

Mehdi Bugnard Over a year ago

if i set @retval = '1234' , i get correctly a string with1234 . I suppose he try to Parse the result as int. But why ? . Have no solution for return a full string value as an output?

Neel Over a year ago

Are you using "return" statement at the end?

Mehdi Bugnard Over a year ago

Hi , thank you take time for me. No i removed totally the 'return'. But now the output always equal to NULL. I add a new screenshot of my storeproc and code c# in the description. Thank you again

Neel Over a year ago

it must be something from C# code side as your SP looks fine

|

Mehdi Bugnard · Accepted Answer · 2016-07-25 13:37:48Z

0

I find, Finally i use (ExecuteStoreQuery) instead of (ExecuteStoreCommand). Where i put my sql directly Inside. And it's return me the string result of my select. Easy . Thank you for try to help me ;-) .

public virtual string GetCardCodeLinkedToAttestation(string atBuisnessCode)
    {
        string return_value = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<string>
        (string.Format("SELECT TOP 1 [CardCode] FROM [SC-SAP01].[SWC_PROD].[dbo].[OCPR] WHERE [U_Attestation] = '{0}'", atBuisnessCode)).FirstOrDefault();


        return string.IsNullOrWhiteSpace(return_value) ? string.Empty : return_value;
    }

answered Jul 25, 2016 at 13:37

Mehdi Bugnard

3,9875 gold badges51 silver badges89 bronze badges

Collectives™ on Stack Overflow

ExecuteStoreCommand can't return string with SqlParameter output

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related