Stored procedure


#1

Hello everyone, I want to execute an sql server stored procedure:

yield Database.raw(‘Exec my_stored_procedure @param1=’+param1+’,@param2=’+param2)

and it throws me the error:

“Conversion failed when converting date and/or time from character string”

But I don’t have any date and time between my params, all they’re numbers and strings

which could be the error?


#2

I really can’t answer that directly, because I’m not sure what the problem is based on the information you gave me… but think about this:

  1. Procedures are related to business rules;
  2. Business rules should be on backend code, not database;
  3. How would you scale your app into microservices, for example? In most cases, you would necessarily have to transform your procedures into scheduled tasks, once again having the business rules in the backend.

Maybe don’t use procedures at all? Adonis is so friendly, take advantage of that.


#3

This is a remarkably unhelpful reply!
You are correct to say that business rules belong in the database, but that means that sometimes you have to pass somewhat complex data to a stored procedure, which seems to be what’s going on here.
I am about to use stored procedures extensively for this purpose so would appreciate some further information.
Perhaps the op’s problem is some sort of syntax error?


#4

I think one of your params is expecting a datetime instance and your params is probably and string that could not be casted to a datetime instance


#5

Looks like bad syntax. Should probably be:

Database.raw('Exec my_stored_procedure @param1=?, @param2=?', [param1, param2])

Even if shoving variables directly into a query string were allowed that’s how you get sql injections, use replacement tokens, always.