Help with executing sql server stored procedure from ecto

Questions / Help
,
1

I have run into a little issue and am not sure what I’m doing wrong. My app is connected to an external sql server database (database for another app) via Tds Ecto. I’m trying to execute a stored procedure using Repo.query but nothing is being returned and no errors are reported. Here is my code:

case SARepo.query("""
  sma_SP_Get_CaseNotes @CaseID=@1;
""", [case_id]) do 
  {:ok, %Tds.Result{columns: columns, rows: rows}} ->
    {:ok, Helpers.map_rows_to_columns(rows, columns)}
  {:error, %Tds.Error{} = error} ->
    {:ok, error}
end

When I run the sma_SP_Get_CaseNotes procedure in DataGrip it works fine and returns the data I want. I’ve run a trace in Sql Server Data Profile and the difference between the datagrip call and the Ecto call appears to be that Ecto is making an RPC type call whereas datagrip is doing something a little different that I am unfamiliar with. Anyone see an obvious mistake I’m making?

1 Like
2

Try adding the exec command before the name of the stored procedure.
When you write Transact-SQL, that’s the way to execute stored procedures so I am thinking it might be the same way in your case.

1 Like
3

The issue was actually that I needed to add:

SET NOCOUNT ON;

To the top of the query so that it would return a result.

4 Likes