4 ms·
We in-line our sql code in our c# code and avoid dynamic sql in stored procedures at all costs. We write our queries to handle all the parameters the user can c
by bigtex 3y ago
We in-line our sql code in our c# code and avoid dynamic sql in stored procedures at all costs. We write our queries to handle all the parameters the user can choose and rarely have problems with parameter sniffing which we know how to easily resolve. I did need to create a pivot query with a dynamic number of columns, so I use C# to generate the query and then pass the final result to the server using Dapper.
- twoquestions 3y agoThis is the way. I inherited a very long SP that built a SQL string within the procedure then `sp_executesql`'ed the resulting string. It was also at the very core of our product, which made it a living nightmare to support, because if you messed anything up all Hell would break loose. I spent a good couple months translating it to C#, and making a few improvements while I was at it. Absolutely 100% worth it, those improvements made it possible to add features to make supporting the product much easier for frontline support to help people. The only benefit is we could cowboy updates directly to prod instantly. If that made your heart skip a beat, congrats, you actually give a damn about change management!