MSSQL CONCAT with NULL in string

Question

I need no send parameters into function, and I generate code dynamically. If variable type is INTEGER and value is NULL, than I have '', but I need 'null'. Please help me to find normal decision:

DECLARE @a INT = NULL, @b INT = 5;

SELECT CONCAT('SELECT dbo.fncName(',@a,',',@b,')') AS Result;
--result  "SELECT dbo.fncName(,5)",
--I need: "SELECT dbo.fncName(NULL,5)"
GO

-- it works, but it looks very bad:
DECLARE @a INT = NULL, @b INT = 5;

DECLARE @a1 VARCHAR(16) = IIF(CAST(@a AS VARCHAR(16)) IS NULL,'NULL',CAST(@a AS VARCHAR(16)));
SELECT CONCAT('SELECT dbo.fncName(',@a1,',',@b,')') AS Result;
--result "SELECT dbo.fncName(NULL,5)"

Answer 1

You need dynamic SQL for this. You can either use sp_executesql

DECLARE @a INT = NULL, @b INT = 5;

DECLARE @sql nvarchar(max) = CONCAT(
  'SELECT dbo.',
  QUOTENAME(@procName),
  '(@a, @b) AS Result;'
);
EXEC sp_executesql @sql,
  N'@a int, @b int',
  @a = @a,
  @b = @b;

Or even better: scalar functions can actually be called directly like procedures.

DECLARE @a INT = NULL, @b INT = 5, @result INT;

EXEC @result = @procName
  @a = @a,
  @b = @b;

SELECT @result AS Result;