CODEWITHSUNDEEP
t-sqlsp-executesql
Cameron Castillo
Cameron Castillo

Reputation: 2832

Output parameter from TSQL sp_executesql seems to be null

I'm sure there's a simple explanation. I'm trying to print out the Max ID for all tables with an Identity column.

The TSQL code below seems to run fine in the sense that the Results window contains cells with values, but the Messages window does not show: Table1 Row Count = 99

Seems as if the @Result variable is NULL when it gets to the PRINT command.

Am I missing something obvious or is the OUTPUT command working differently than I expect?

DECLARE @TABLE VARCHAR(100)
DECLARE @FIELD VARCHAR(100)
DECLARE @SQL NVARCHAR(300)
DECLARE @result  BIGINT

DECLARE CUR CURSOR FOR
SELECT 
  [table] = t.name,
  [colname] = c.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.identity_columns c ON c.object_id = t.[object_id]

OPEN CUR

FETCH NEXT FROM CUR INTO @TABLE, @FIELD

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'SELECT MAX([' + @FIELD + ']) FROM [' + @TABLE + ']'

    EXEC sp_executesql @SQL, 
                       N'@result int OUTPUT', 
                       @result OUTPUT
    PRINT @TABLE + ' Row Count = ' + CONVERT(nvarchar, @result)
    --NOTHING SEEMS TO BE PRINTED HERE

    FETCH NEXT FROM CUR INTO @TABLE, @FIELD
  END

CLOSE CUR
DEALLOCATE CUR

Upvotes: 0

Views: 24

Answers (0)

Related Questions