CODEWITHSUNDEEP
sql-servervbastored-procedures
BellaFiorenza
BellaFiorenza

Reputation: 1

MS Access VBA interpreting string from SQL as int?

I have a SQL Server stored procedure that returns a varchar value to Access VBA, but I'm getting an error from Access saying it can't convert a varchar value to int. Nowhere is the return value specified as an integer, whether the procedure or in VBA. I've simplified the actual code for the sake of this post. The below is not the full code.

Stored procedure:

create procedure proc 
@CreateOrUpdate varchar(6) output
as
begin
select @CreateOrUpdate = 'Create'
return @CreateOrUpdate
end

In Access VBA:

Dim Com As ADODB.Command
Set Com = New ADODB.Command
With Com
    .ActiveConnection = Cnn  (Set elsewhere - No problems with this)
    .CommandType = adCmdStoredProc
    .CommandText = "proc"
    .Parameters.Append .CreateParameter("@CreateOrUpdate", adVarChar, adParamOutput, 6)
End With

Result: "Conversion failed when converting the varchar value 'Create' to data type int."

I have other return values that are also varchar with identical syntax and I'm not having any problems with them. Any help is appreciated.

Upvotes: 0

Views: 169

Answers (1)

Thom A
Thom A

Reputation: 96004

The problem is your attempt to use RETURN to return a value for an OUTPUT parameter. RETURN is used to denote the success of a Procedure; 0 for success and anything else for failure. RETURN returns an int, so RETURN @CreateOrUpdate will implicitly try convert @CreateOrUpdate to an int (hence the failure).

Don't RETURN the OUTPUT value, just assign it:

CREATE PROC MyProc @CreateOrUpdate varchar(6) OUTPUT AS
BEGIN
    SELECT @CreateOrUpdate = 'Create';
END;

Though the above it completely pointless. What is it trying to achieve..?

Upvotes: 2

Related Questions