Write Routines Directly in .NET Languages - by Jason Kresowaty

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 318327 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 12/17/2007 4:02:52 PM
Access Restriction Public

Description

This is a suggestion to implement a feature where routines (such as stored procedures, functions, etc.) may be created directly in a CLR language without explicitly adding an assembly which was compiled separately (i.e., in Visual Studio).

This would be extremely convenient for simple routines; it would make development, testing, and maintenance much easier for some common scenarios.

For example:

CREATE FUNCTION MyFunc
(
    @param INT
)
RETURNS INT
LANGUAGE C#
AS
public static SqlInt32 MyProc(SqlInt32 param)
{
return param * (9/5) + 32
}

/* Note: Should be possible to define additional private C# methods here as well, so that the above method can call them as helper methods. */
Sign in to post a comment.
Posted by spongman on 6/30/2015 at 5:00 PM
Vineet, now that Roslyn is a thing, this should be easy to do, right? You can embed the compiler right in the engine, you can put syntax coloring/intellisense inside ssms, and we won't have to be messing about with DLLs and registration. the source code for T-SQL AND C# procedures can live in the same place. a full install of VS shouldn't be necessary for this, it should all be in ssms.
Posted by J-S-B on 3/31/2015 at 6:12 AM
There is another post similar to this one here: https://connect.microsoft.com/SQLServer/feedback/details/265266/add-server-side-compilation-ability-to-sql-clr

I too want this, and fortunately, there is at least one way to accomplish this, but it would be nicer is Microsoft just provides it.

That said.
One can make a single CLR stored procedure (SP), that uses the Razor Engine, to dynamically build an assembly at run time. from CSHTML.
This one single CLR SP or "Master SP" can then do one of two things:
1) It could be used to provide a "create procedure" equivalent, which would probably be the best.
2) Or, it could be cut/copy/pasted to re-use the Razor Engine, in each made SP.

In case (1), one would have CSHTML code for a SP, in a standard manner that the Master SP "understands", and it would take the C# code, build an assembly dynamically, and store it onto the SQL-Server without fuss, and manage it as one goes to the equivalent of "create procedure" "drop procedure" "alter procedure", etc. The Master SP, would fully create a new SP, with the parameters defined in the CSHTML from some standard.

In case (2), this would be slower to run, but would have the advantage of there being literally only 1 SP, the Master SP. And it would use maybe a temp table, to store the C#/CSHTML code, and then just store the Assembly into an application cache (which SQL-Server may fight), and manage the cache from "create procedure", "alter", "drop", etc. The slowness comes in, that the assemblies would not necessarily be built by the Razor until used once, and of course, extra logic in the Master SP, would have to detect which Razor-built-assembly to use, each time it was called, not to mention the Master SP's parameters would not be a custom fit for the specific of each need.

Well, those are some ideas. If I get time, I'm going to try to build one.
Posted by Microsoft on 12/21/2007 at 12:07 PM
Hello,

Thank you for sending your feedback to us. One of the great benefits and reason why customers use CLR integration is the ability to use rich tools like Visual Studio for development. The code customers usually write in stored procedures and functions is usually not very simple and rich features like intellisense etc. in Visual Studio help a lot in writing code efficiently.
However, i do see the benefits of what you are suggesting:
1. I already have the code and i want to use this in SQL. Why build assembly and register it (you have to issue create function statement in both cases). For this, you can always use the SQL Server project in Visual Studio and click deploy (which would build, register the assembly/source and also register the function). The reason you would still want to do the way you are suggesting is if you dont have Visual Studio professional or you dont want to use it which is understandable.
2. Even after i upgrade to future SQL Server which uses future version of the CLR, i want SQL Server to always have the source code and automatically re-compile if needed and use that. Although SQL Server allows you to add your source code using ALTER ASSEMBLY command (note that Visual Studio does that automatically when you deploy), it does not ensure that the source code you are adding is indeed what is in the assembly bytes. Your suggestion would make that happen.

Ofcourse this requires us to add the ability in the server to understand the .NET compilers and compile. With so many .NET languages, it means SQL has to have the compilers for all of them (or may be decide on a supported set). This also requires us to figure out whether to recompile when the CLR is upgraded and what to do if the recompile fails.

Overall, this is an enhancement that we agree we should make and we will consider it in a future release of SQL Server (after SQL Server 2008).

If there are other reasons why you would like to have this functionality, please let us know.

Thanks,
-Vineet Rao.