HOW TO: call stored procedures with Tds Library in Elixir

Question

I get the following error trying to call a stored procedure using the Tds library for Elixir

The stored procedure get_account exists and has exactly one parameter @id

iex(5)>Tds.Connection.query(pid, "get_account",[%Tds.Parameter{name: "@id", value: 1}])                                                        
{:error,
 %Tds.Error{message: nil,
  mssql: %{class: 16, length: 252, line_number: 0, msg_text: "Procedure or function 'get_account' expects parameter '@id', which was not supplied.", number: 201, proc_name: "get_account",
    server_name: "localhost\\SQLEXPRESS", state: 4}}}
iex(6)>

Trying this with Tds.proc(pid, "get_account",[1]) does not work either

This is probably one of those cases where it's easier to drop down to the Erlang ODBC layer and do what you're trying to do. See this question (http://stackoverflow.com/questions/18929391/how-to-call-stored-procedure-taking-array-using-odbcparam-query-in-erlang) for more details. — Onorio Catenacci, Apr 18 '16 at 12:25

Charles Okwuagwu · Accepted Answer · 2016-05-07T16:45:44.320

Workaround:

Tds.query(pid, "get_account 1",[])

Use this the same way you would pass parameters directly to a stored proc using EXEC.

Updated:

This format also works:

 params = [
  %Tds.Parameter{name: "@1", value: 100, type: :integer},
  %Tds.Parameter{name: "@2", value: 100, type: :integer},
  %Tds.Parameter{name: "@3", value: <<0 ,0 ,0 ,0>>, type: :binary},
]
  Conn.query(s.db, "save_auth_key @1, @2, @3", params)

HOW TO: call stored procedures with Tds Library in Elixir

1 Answers1