Stored procedures have been falling out of favour for several years now. The preferred approach these days for accessing a relational database is via an O/R mapper such as NHibernate or Entity Framework. Stored procedures require much more work to develop and maintain. For each table, you have to write out individual stored procedures to … Read more
In my opinion, Stored Procedures should be used solely for data manipulation when the same routine needs to be used amongst several different application or for ETL between databases or tables, nothing more. Basically, do as much in code as you can until you run into the DRY principle or what you are doing is … Read more
Here’s the mysql reference for cursors. So I’m guessing it’s something like this: DECLARE done INT DEFAULT 0; DECLARE products_id INT; DECLARE result varchar(4000); DECLARE cur1 CURSOR FOR SELECT products_id FROM sets_products WHERE set_id = 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO products_id; IF NOT … Read more
You need to create a model class that contains all stored procedure properties like below. Also because Entity Framework model class needs primary key, you can create a fake key by using Guid. public class GetFunctionByID { [Key] public Guid? GetFunctionByID { get; set; } // All the other properties. } then register the GetFunctionByID … Read more
Just drop the procedure if it does exist and then re-add it: DROP PROCEDURE IF EXISTS my_procedure; CREATE PROCEDURE my_procedure()
This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance – parameter sniffing There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time. The recommended fix for most situations (though it depends on the structure of … Read more
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
Here are my stored procedure error-handling guidelines. Call each stored procedure using its fully qualified name to improve performance: that’s the server name, database name, schema (owner) name, and procedure name. In the script that creates each stored procedure, explicitly specify which roles are allowed to execute the procedure ,eg public or whatever. Use sysmessage, … Read more
An inline table valued function (TVF) is like a macro: it’s expanded into the outer query. It has no plan as such: the calling SQL has a plan. A multi-statement TVF has a plan (will find a reference). TVFs are useful where you want to vary the SELECT list for a parameterised input. Inline TVFs … Read more
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments. Pass XML into the stored procedure Pass a table-valued parameter into the stored procedure 1. Pass XML into the stored procedure With XML passed into a … Read more