Are SQL Server CLR and T-SQL stored procedures identical to the caller?

Question

To write some dummy stored-procedures I was wondering if I could just write them in C# since I don't know T-SQL very well.

Are C# and T-SQL versions of the same procedure indistinguishable to calling code e.g. C++/C#/Java code using standard database library classes?

Answer 1

Yes, the caller won't know the difference. However, there are a few API differences:

• Parameters of LOB types (i.e. NVARCHAR(MAX), VARBINARY(MAX), and XML) cannot have default values in SQLCLR objects. (Microsoft Connect suggestion to vote for, perhaps: Support default parameter values for NVARCHAR(MAX), VARBINARY(MAX), and XML types in SQLCLR objects )
• You cannot pass in TVPs to SQLCLR objects.
• You cannot return the following datatypes from SQLCLR objects:
  • SMALLDATETIME
  • SMALLMONEY
• You cannot pass in or return the following datatypes to / from SQLCLR objects:
  • CHAR
  • VARCHAR
  • TEXT
  • NTEXT
  • IMAGE

Outside of that technical answer to the Question, there are some things to consider:

• What specifically do you feel is easier to do in C# than in T-SQL?

• What exactly is being considered "dummy"? Are you just needing a signature? If so, it would be far easier to do that in plain T-SQL. All it takes for T-SQL to do a mock stored procedure is:

  CREATE PROCEDURE SchemaName.ProcedureName
  (
    @Param1name  Param1Type,
    @Param2name  Param2Type,
    ...
  )
  AS
  SELECT  CONVERT(resultField1type, NULL) AS [resultField1name],
          CONVERT(resultField2type, NULL) AS [resultField2name],
          ...
              ;
  

• The term "dummy" implies that these will be replaced with something else later. What will they be replaced with? T-SQL Stored Procedures? Who will write those if you don't know T-SQL?

• If you don't know T-SQL, and you don't have anyone helping you that does know T-SQL (hence needing to make the "dummy" stored procedures in SQLCLR), then how do you know that you are structuring this solution correctly? You could be going in the completely wrong direction, regardless of T-SQL vs SQLCLR.