I have problem executing SP with Dapper using ODBC Sybase, for input parameter I do not have any problems, when I tried out parameter is not working as expected.
Original SP have insert statement so I put TRANSACTION statement in the code.
Here my modified SP:
create proc dbo.SP_TEST_DAPPER(@FIRST_PARM VARCHAR(10), @ErrorMessages VARCHAR(1000 ) Out )
AS
BEGIN
declare @Rtn Int
declare @TranName varchar(50)
SELECT @TranName = 'SP_TEST'
BEGIN TRANSACTION @TranName
IF @FIRST_PARM = '1'
BEGIN
SELECT @ErrorMessages = 'OK'
END
BEGIN
SELECT @ErrorMessages = 'Error'
GOTO ERROR_TRAP
END
COMMIT TRANSACTION @TranName
SELECT @ErrorMessages
ERROR_TRAP:
ROLLBACK TRANSACTION @TranName
SELECT @ErrorMessages
END
Your implementation for ODBC named parameters is incorrect. You encase the named parameter with question marks in your statement and create the named parameter without the question marks. The question marks are used by Dapper to parse the statement to find the names.
storedProcedureName = "dbo.SP_TEST_DAPPER ?FIRST_PARM?, ?ErrorMessages?";
pX.Add("FIRST_PARM",firstparm);
pX.Add("ErrorMessages", dbType: DbType.String, direction: ParameterDirection.Output, size: 1000);
See this answer for more information: https://stackoverflow.com/a/26484944/6490042