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

sql-server

Solution

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

Problem

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 ```

Original source