Search code examples
sql-servert-sqlazure-sql-database

sp_executesql OUTPUT returning null

Question: OUTPUT variable in the following code is always null despite the fact that the dynamic SQL executes successfully and shows the correct result in SSMS (as shown in the image below). What could be the issue and how can we fix it?

Remark: Online search shows multiple posts (such as this and this). But they seem to have special cases (special characters etc.). But following example does not have any such special case, and I'm not sure what is the issue here.

--create table testTable(mycol varchar(10))
--insert #testTable values ('abc'), ('xy4z')
--select * from testTable
---------------------
DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @tableName NVARCHAR(150)
SET @tableName = 'testTable'
DECLARE @ColName NVARCHAR(150)
SET @ColName = 'mycol'
DECLARE @colMaxLen TINYINT

SET @sqlCommand = CONCAT(N'SELECT MAX(LEN(', @ColName, ')) FROM ', @tableName)
select @sqlCommand
EXEC sp_executesql @sqlCommand, N'@tableName NVARCHAR(150), @ColName NVARCHAR(150), @colMaxLen TINYINT OUTPUT', @tableName = @tableName, @ColName = @ColName, @colMaxLen = @colMaxLen OUTPUT
SELECT @colMaxLen

enter image description here

Solution

  • you did not assign the variable @colMaxLen to the expression MAX(LEN.... at all in your dynamic query.

    To do that, change the following

    SET @sqlCommand = CONCAT(N'SELECT @colMaxLen = MAX(LEN(', @ColName, ')) FROM ', @tableName)