We (musicpictures.com / eviscape.com) wrote that django snippet but its not the whole story (actually that code was only tested on Oracle at that time). Stored procedures make sense when you want to reuse tried and tested SP code or where one SP call will be faster than multiple calls to the database – or … Read more
One way is to use the Database property off the DbContext: SqlParameter param1 = new SqlParameter(“@firstName”, “Frank”); SqlParameter param2 = new SqlParameter(“@lastName”, “Borland”); context.Database.ExecuteSqlCommand(“sp_MyStoredProc @firstName, @lastName”, param1, param2); EF5 definitely supports that.
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter. create table batch(batchid number, batchname varchar2(30), batchtype char(1), source char(1), … Read more
If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do: Exec secondSPName @anyparams Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with … Read more
Same as your other code, add a “Database project” to your application solution and keep the sql files used to build the database objects in there. Use the same version control for those code files as you do for the application.
The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in … Read more
If a statement can return no or multiple results, you should not use executeQuery, but execute() instead, this method returns a boolean indicating the type of the first result: true: result is a ResultSet false : result is an update count If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise … Read more
@michal kralik – unfortunately there’s a bug with the MySQL C API that PDO uses which means that running your code as above with some versions of MySQL results in the error: “Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable”. You can … Read more
Figured it out. This is not a bug with PHP (though it used to be) – it’s a bug in some versions of phpmyadmin. The same bug intermittently reappears and is then fixed in various subversions (see above): #1312 – PROCEDURE [name] can’t return a result set in the given context This behavior appears limited … Read more
The reserved prefix that exhibits this behaviour is sp_. Not sp. A stored procedure called spAddUser will be resolved in the normal way without looking for a matching object in master. The relevant quote in books online is Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server … Read more