Extended Stored Procedures (XPs) have been deprecated as of the release of SQL Server 2005 and new projects should not be using that API.
This can be done easily via SQLCLR, and yes, you can return a string (or most datatypes) from a C# method. The only construct restricted to returning an INT is a Stored Procedure, and that holds true for both T-SQL and SQLCLR Stored Procedures.
You would use something along the lines of:
public class Stuff
public static SqlString DeleteFile ([SqlFacet(MaxSize=500)] SqlString FilePath)
string _ReturnMessage = String.Empty;
_ReturnMessage = "some text here";
catch (Exception _Error)
_ReturnMessage = _Error.Message;
return new SqlString(_ReturnMessage);
When you create the T-SQL wrapper object, be sure to specify the
RETURNS NULL ON NULL INPUT option so that it won't even both executing if a
NULL is passed in (you should probably add additional validation to the method, such as returning if an empty string is passed in, etc):
CREATE FUNCTION dbo.DeleteFile
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME.... ;
Since the Assembly will need to be marked as
PERMISSION_SET = EXTERNAL_ACCESS, please do not set the Database to
TRUSTWORTHY ON. Instead you should sign the Assembly, create the Asymmetric Key or Certificate in
[master], create a Login from that Asymmetric Key or Certificate, and then grant that Login the
EXTERNAL ACCESS ASSEMBLY permission (if using SQL Server 2005 - 2016) or the
UNSAFE ASSEMBLY permission (starting with SQL Server 2017).
For detailed instructions for doing this via a build process, you can try either of the two techniques I describe in the following blog posts (both work fully with SSDT or independently):
Also consider supporting my Asymmetric Key suggestion which would eliminate most of the hassle of publishing signed Assemblies:
Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE
OR, if you would rather not deal with any of this, there is a File_Delete function in the SQL# SQLCLR library (that I wrote) that does this. It even returns a string, which is the error message if an error occurs, else is just an empty string. No way to have it pass back a custom string on success, though. Also, please note that while there is a Free version, the File System functions are only available in the Full version. But the security aspect would all be handled cleanly and properly, and there is even a T-SQL Stored Procedure, that exists in the same DB as the SQLCLR code, that will set up the
Asymmetric Key and associated
[master] (which could help with deployments to new systems by not requiring a full install, as long as this Stored Procedure was deployed and executed prior to deploying any Assemblies (assuming an environment of SQL Server 2017)).