try
{
return Connection.QuerySingleOrDefault<T>(sql, param, _transaction,
commandType: CommandType.StoredProcedure);
}
catch (Exception orig)
{
var ex = new Exception($"Dapper proc execution failed!", orig);
AddDetailsToException(ex, sql, param);
throw ex;
}
With SQL:
CREATE OR REPLACE PROCEDURE public."GetChildBank"(
"bankId" integer DEFAULT NULL::integer)
LANGUAGE 'sql'
AS $BODY$
)
select * from cte where ParentBank is not null
and "Id" <> "bankId"
$BODY$;
I am using Dapper with PostgreSQL and using stored procedure to get data but it always throws errors.
It converts into a SQL statement
SELECT *
FROM "GetChildBank"("bankId" := $1)
which is wrong.
Dapper does not seem to be the problem here. To execute a stored procedure in postgresql with parameters it should be
using (var connection = new NpgsqlConnection("Host=localhost;Username=postgres;Password=root;Database=sample"))
{
connection.Open();
var sql = "CALL \"GetChildBank\"(@bankId)";
var p = new DynamicParameters();
p.Add("@bankId", 11);
var res = connection.Execute(sql, p);
}
To create a function that returns a row set and to callit from select, you need to use create function
CREATE OR REPLACE FUNCTION ""GetChildBank"("bankId" integer) RETURNS SETOF cte AS $$
SELECT * FROM cte where "ParentBank" IS NOT NULL AND "Id" <> $1;
$$ LANGUAGE sql;