422,757 Members | 1,365 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,757 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Sep 6 '17 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,204
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.
Sep 6 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.