SQL Server - Variable declared but still says "Must declare the scalar variable"

Question

I'm trying to run this set of SQL commands on Microsoft SQL Server but I am getting this error:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@dbstatus".

I thought I did declare the variable so I'm not sure why it's still throwing the error?

DECLARE @dbname nvarchar(100) 
DECLARE @dbstatus varchar(500) 
DECLARE @sqlCommand NVARCHAR(1000) 
create table #temptable (dbname nvarchar(100), status varchar(500))

DECLARE c1 CURSOR READ_ONLY 
FOR 
   SELECT '[' + name + ']'   FROM sys.databases WHERE name = 'EDDS1084543'

OPEN c1  
FETCH NEXT FROM c1 INTO @dbname 

WHILE @@FETCH_STATUS = 0 
BEGIN            
      SET @sqlCommand = 'SET @dbstatus = (SELECT Status FROM ' + @dbname + '.[EDDSDBO].[dtSearchIndex])'   

      EXECUTE sp_executesql @sqlCommand

      INSERT INTO #tempTable(dbname, [status])VALUES (@dbname, @dbstatus)

      FETCH NEXT FROM c1 INTO @dbname   
END   

CLOSE c1 
DEALLOCATE c1 

Answer 1

The problem is here:

  SET @sqlCommand = 'SET @dbstatus = (SELECT Status FROM ' + @dbname + '.[EDDSDBO].[dtSearchIndex])'   
  EXECUTE sp_executesql @sqlCommand

This causes the server to execute the value of @sqlCommand as a standalone statement. Within this statement, @dbstatus has not been declared as a variable, hence the error. This is what's getting executed:

SET @dbstatus = (SELECT Status FROM [value in @dbname].[EDDSDBO].[dtSearchIndex])

Try this instead:

WHILE @@FETCH_STATUS = 0 
BEGIN            
      SET @sqlCommand = 'SET @dbstatus = (SELECT Status FROM ' + @dbname + '.[EDDSDBO].[dtSearchIndex])'   
      EXECUTE sp_executesql @sqlCommand, '@dbstatus varchar(500) output', @dbstatus output
      INSERT INTO #tempTable(dbname, [status])VALUES (@dbname, @dbstatus)
      FETCH NEXT FROM c1
      INTO @dbname   
END  

Answer 2

EXEC/sp_executesql creates a new connection (SPID) to the SQL Server, which is not your current session, so it cannot see the variable. Check the documentation.

Basically, you have to declare the parameter you want to pass into the call, and give it a value. In this case, both have to include the OUTPUT specifier.

  EXECUTE sp_executesql @sqlCommand, '@dbstatus varchar(500) output', @dbstatus output