Manage SQL Server stored procedures in Visual Studio

Question

It is well known that perfomance wise, it is recommended to use SQL Server stored procedures instead of inline code. However, I still use inline SQL queries in Visual Studio for various reasons:

• The queries can be neatly organized in separate text files (.sql) and in a folder structure.
• The files are part of the Visual Studio solution and thus submitted to source control.
• Changes to SQL queries can be published together with the applications (using WebDeploy for ASP.NET apps or ClickOnce for Windows apps).
• There is no need to synchronize changes to the SQL queries and publishing new versions of applications.
• It enables me to work on the SQL queries even when I am offline (or without access to that particular SQL Server).

I am not quite ready to give up these advantages but still, I am aware that I am sacrificing performance.

Is there a way to get the best of both worlds?

Thanks in advance for any insights.

Chris

Answer 1

I still use inline SQL queries in Visual Studio ...

But how? Context is important here. VS is just a tool. If you use inline queries in your app, then you have a potential security risk if you are not careful about how you implement them (re: sql injection). In addition, the use of inline queries requires the appropriate permissions to database objects - another security risk. And this approach creates a dependency between your code and the schema - which is minimized by using procedures.

Answer 2

Literally every single one of your points can be provided by Stored Procedures too... Not only could you just have a .sql file with the CREATE or ALTER command for the stored procedure in the exact same way you manage it now, but you could go a step further and use a SQL Database Project type to deploy them in a better manner...

https://msdn.microsoft.com/en-us/library/xee70aty(v=vs.140).aspx

But I will note that stored procedures are not automatically better for performance... If you read this is probably refered to the fact that they are easier to parameterize, so the plans can be resued. Using proper Parameterized queries you will have the same benefits, so I think the basic premise of your question is incorrect.