CODEWITHSUNDEEP
sql-server
SunMan
SunMan

Reputation: 224

What to use for calling functions manually -> SELECT or EXEC?

Why do I have to use a schema (dbo.xxx ) with scalar functions when using SELECT and not when using EXEC?

DECLARE @ReplicationGroup nvarchar(255) = 'Replication Configuration';
SELECT dbo.fnGetReplicationGroupID(@ReplicationGroup) -- Works but I have to keep dbo.
EXEC fnGetReplicationGroupID @ReplicationGroup -- Works without dbo.

select dbo.fnIsDebugLoggingEnabled(); -- Works but I have to keep dbo.
EXEC fnIsDebugLoggingEnabled  -- Works without dbo.

Upvotes: 1

Views: 576

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46241

SQL Server has 3 different types of functions: Scalar, Table-Valued, and Multi-Statement Table-Valued. Scalar functions may be used wherever a scalar expression is allowed as well as in an EXECUTE statement. Table-valued functions and Multi-Statement Table-Valued functions may be used wherever a table-valued expression is allowed, which does not include an EXECUTE statement. Examples:

CREATE FUNCTION dbo.fnScalarFunctionExample(@value int)
RETURNS bit
AS 
BEGIN
    RETURN @value;
END;
GO

CREATE FUNCTION dbo.fnInlineTableValuedFunction (@value int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT @value AS Value  
);  
GO  

CREATE FUNCTION dbo.fnMultiStatementTableValuedFunction (@value int)  
RETURNS @Table TABLE   
(  
    Value int NOT NULL  
)  
AS  
BEGIN  
    INSERT INTO @Table VALUES(@value);
    RETURN;
END;
GO

--scalar functions can be called with EXEC or wherever in a scalar experession is permitted
DECLARE @ReturnValue int;
EXEC @ReturnValue = dbo.fnScalarFunctionExample 1;
PRINT @ReturnValue;
GO
SELECT dbo.fnScalarFunctionExample(1);
GO
SELECT 1 WHERE dbo.fnScalarFunctionExample(1) = 1;
GO

--table-valued functions are invoked from a SELECT query
SELECT * FROM dbo.fnInlineTableValuedFunction(1);
SELECT * FROM dbo.fnMultiStatementTableValuedFunction(1);  
GO

Syntactically, you specify parenthesis (empty if no parameters) when invoking functions with the exception of and EXECUTE statement. Parens are not specified with EXECUTE and parameters, if any, are supplied as named or positional parameters just like when executing a stored procedure.

I don't often see scalar functions invoked with EXECUTE in the wild since one can always use SELECT instead.

EDIT

It is necessary to (at least) schema-qualify scalar functions using 2-part names according to the documentation. As to why this is necessary, I think that is to avoid ambiguity with system functions that might have the same name.

Upvotes: 2

Related Questions