CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user2498668
user2498668

Reputation: 125

Convert multi-statement table valued function to inline TVF

I currently have a multi-statement TVF that I'd like to convert to an inline TVF for performance reasons and I'm having some difficulty with it. I've read through quite a few other questions regarding similar issues, but I'm still having difficulty getting mine to work.

Here's my current multi-statement:

CREATE FUNCTION CN.Sample (@format nvarchar(100),  @oldText nvarchar(max))
RETURNS @Results TABLE (newtext nvarchar(max))
AS
BEGIN
   DECLARE @WebServiceURL nvarchar(256);
   DECLARE @pUrl nvarchar(256);
   DECLARE @pFP nvarchar(256) = NULL;
   DECLARE @enableMC bit = 'true';
   DECLARE @allowShort bit = 'false';
   DECLARE @identity nvarchar(100);
   DECLARE @allowRNG bit = 'false';
   DECLARE @UserName nvarchar(100) = '';
   DECLARE @Password nvarchar(100) = '';
   DECLARE @FileCache nvarchar(256) = '';
   DECLARE @Status varchar(10);

   SELECT  @WebServiceURL = WebServiceURL,
           @pUrl = PURL, 
           @identity = IdentityName ,
           @UserName = UserName,
           @Password = [Password],
           @FileCache = FileCache
   FROM [CN].[VCC]
   WHERE FormatName = @format;

   INSERT INTO @Results(newtext)
   SELECT data 
   FROM dbo.CLRSample (@WebServiceURL,@UserName,@Password,@format,@oldText);
   RETURN;

END

GO

select d.*
from dbo.EE
cross apply CN.Sample ('asdf',  EE.text) d

The CLR function that it's referencing:

CREATE FUNCTION [dbo].[CLRSample](@serviceURL [nvarchar](256),@userName nvarchar(100), @Password nvarchar(100),@format [nvarchar](100),@oldText nvarchar(max))
RETURNS TABLE (data nvarchar(500) )
AS 
EXTERNAL NAME [Sample.SqlClr].[SQLCLR.SampleFunctions].[Sample];
GO

If anyone has any insights on how to effectively convert this into an inline TVF, I'd appreciate it.

Additionally, I have a secondary question. In my reading it's been made pretty plain that inlines are preferred to multi-statements "when possible," but this implies there are scenarios where use of an inline statement isn't possible. I'm unsure what such a scenario would look like and why it would exist. If anyone has any insights or examples regarding this as well, that would also be appreciated.

Upvotes: 0

Views: 483

Answers (1)

Balde
Balde

Reputation: 590

Removing all unnecessary variables:

CREATE FUNCTION CN.Sample (@format nvarchar(100),  @oldText nvarchar(max))
RETURNS TABLE AS  
RETURN
(
   SELECT 
       (SELECT data FROM dbo.CLRSample (WebServiceURL, UserName, [Password], @format, @oldText)) AS data
   FROM [CN].[VCC]
   WHERE FormatName = @format      
)
GO

Upvotes: 2

Related Questions