I'm in a process of transitioning from dynamic queries that were generated in C# code to using Stored Procedures. Although i haven't done any analysis of time difference between the two. But i do have few reasons to move to stored procedures:
1) I work on a security application and i heard of SQL injection. This is one of the most common problems with dynamic queries. But if i use stored procs, I won't have to worry about Sql injections, since queries in Stored Procs are precompiled. 2) Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component. 3) Lastly, stored procs add an extra layer of abstraction; i can keep the database access to storage and retrieval and move most of the business logic into a middle tier. Although i have the DB code in place as dynamically generated strings in code. I might have to do a lot of rework to implement stored procs. Is it really worth it to move to stored procs? If it's really a good idea to move my sql queries out of C# code into stored procs. I've the following problems: Problem 1: -------------- I have code in place that generates dynamic query like this: dbCmd.CommandText = "UPDATE Table1 SET " + setClause + " WHERE ID = " + _id; Table1 has about 15 fields and on the basis of what field is changed, i generate the "setclause". Now if i have to do it in Stored Proc, i have to add a parameter for each field and update all the fields on the basis of the criteria. Which would be considered best practice in this case? Problem 2: ------------ I'm also using dynamic queries in case of SELECT queries. For example, i have to fetch records from "Table1" on the basis of "ID" or "Name" or combination of both. dbCmd.CommandText = "SELECT ID, Name, Field3, Field3, ..., " + whereClause + " ORDER BY Name;"; a) To implement this in stored proc, i can either have different stored proc to get data from Table1 on the basis of criteria, or b) i can have all the criteria fields as parameters and have If statements to check which one is set and write a sql query for each case. What would be considered best practice in this case? Thanks.
