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
date
params and then return a table based on that, how would I use the three different parameters? – Pothole