getting error [code: 137, sql state: S1000] Must declare the scalar variable

[Tanusre]

Hi,

I'm using below code to get column list with null value. I'm using dbvisualizer.

DECLARE
@ColumnName nvarchar(500)
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'testtable';

DECLARE getinfo CURSOR FOR
SELECT
c.COLUMN_NAME
,c.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS AS c
WHERE
c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName;

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @TableName + N')'', 0, 0) WITH NOWAIT;';
EXECUTE (@cmd);

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;

CLOSE getinfo;
DEALLOCATE getinfo;


but i'm getting following error:
getting error [code: 137, sql state: S1000] Must declare the scalar variable @ColumnName

[Rabbit]

The code works fine from SSMS. I've never used DBVisualizer but I'm guessing that's where the issue is. It's probably separating the SQL into distinct statements so that the declarations are out of scope.

As a workaround, I would say wrap it into a stored procedure and call the procedure from DBVisualizer.