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
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: