SQL Server

Executing Scalar Functions

There are several ways how to execute scalar functions. A lot of them are well known: using SELECT or SET. But did you know that you can use EXECUTE too? We will play with all the options in this article and especially with EXECUTE we will enjoy it:)

First, create a simple scalar function to play with:

CREATE FUNCTION dbo.f_MergeStrings(
	@String1 VARCHAR(100),
	@String2 VARCHAR(100)
)
RETURNS VARCHAR(200)
AS
BEGIN
	RETURN @String1 + @String2
END
GO

There is no magic: two input strings are concatenated to one.

1. SELECT

SELECT dbo.f_MergeStrings('AB', 'CD')
GO

I will say this is the most common way how we are calling scalar functions to get the result. For sure there can be a complex SELECT statement and a scalar function can be called anywhere inside it where expressions are allowed (in place of columns in SELECT, WHERE, joins, etc.)

For sure we can assign a scalar function return value to another variable:

DECLARE @FinalString VARCHAR(200)

SELECT @FinalString = dbo.f_MergeStrings('AB', 'CD')

PRINT @FinalString
GO

2. SET

SET works very similar to SELECT:

DECLARE @FinalString VARCHAR(200)

SET @FinalString = dbo.f_MergeStrings('AB', 'CD')

PRINT @FinalString
GO

There is one exception: it can´t be called without assigning value to a variable like (syntax error)

SET dbo.f_MergeStrings('AB', 'CD')
GO

3. EXECUTE

Yes, you can call the scalar function using EXECUTE without all the parentheses as you do it for stored procedures:

DECLARE @FinalString VARCHAR(200)

EXEC @FinalString = dbo.f_MergeStrings 'AB', 'CD'

PRINT @FinalString
GO

You can also use long-form with full parameter names:

DECLARE @FinalString VARCHAR(200)

EXEC @FinalString = dbo.f_MergeStrings @String1 = 'AB', @String2 = 'CD'

PRINT @FinalString
GO

It´s really funny that you can execute a scalar function the way that nothing is returned to the client – the result is completely lost:

EXEC dbo.f_MergeStrings @String1 = 'AB', @String2 = 'CD'
GO

You can use a lot of the clauses supported by EXECUTE:

DECLARE @FinalString VARCHAR(200)

EXEC @FinalString = dbo.f_MergeStrings 'AB', 'CD' WITH RECOMPILE

EXEC @FinalString = dbo.f_MergeStrings 'AB', 'CD' WITH RESULT SETS UNDEFINED

EXEC @FinalString = dbo.f_MergeStrings 'AB', 'CD' WITH RESULT SETS NONE
GO

Except RECOMPILE they don´t have any effect because there is no result set to be returned.

If you will try set an explicit result set the statement will fail and the error message is clear:

DECLARE @FinalString VARCHAR(200)

EXEC @FinalString = dbo.f_MergeStrings 'AB', 'CD' WITH RESULT SETS (([String] NVARCHAR(20)))
GO

Execute-Scalar-Functions-With-Result-Sets

Using dynamic SQL you can run the code under different security context:

EXEC ('DECLARE @FinalString VARCHAR(200); 
	   EXEC @FinalString = dbo.f_MergeStrings ''AB'', ''CD''; 
	   PRINT @FinalString') AS LOGIN = 'SQLpowered'
GO

And If you want you can wrap it to sp_executesql:

DECLARE @String1 VARCHAR(100)
DECLARE @String2 VARCHAR(100)
DECLARE @FinalString VARCHAR(200)
DECLARE @Params NVARCHAR(500)

SET @String1 = 'AB'
SET @string2 = 'CD'

SET @Params = N'@String1 VARCHAR(200), @String2 VARCHAR(200), @FinalString VARCHAR(200) OUTPUT'

EXECUTE sp_executesql N'EXEC @FinalString = dbo.f_MergeStrings @String1, @String2', @Params, 
						@String1 = @String1, @String2 = @String2, @FinalString = @FinalString OUTPUT

PRINT @FinalString
GO

Which method is the best? I will say the SET and SELECT are something we are familiar with. EXECUTE is most of the time used for calling stored procedures or dynamic SQL and it can be a big surprise for people to see it used with scalar functions.

Further reading:

Leave a Reply

Your email address will not be published.