How can I resolve an ambiguous column in where clause?

Question

    declare @tempTable table
(
TableSchema nvarchar(256),
TableName nvarchar(256),
ColumnName sysname,
columnDisplayName nvarchar(500),
ColumnType nvarchar(256),
NotNullCnt bigint
);
declare @TableBU table
(
ColumnName1 sysname,
BU nvarchar(500),
CountBU bigint
);
declare @sql nvarchar(4000);
declare @sql1 nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @ColumnType nvarchar(256);
declare @columnName sysname;
declare @columnDisplayName nvarchar(500);
declare @cnt bigint;
declare @calcul bigint;
declare @BU nvarchar(1000);
declare @varraible nvarchar(1000);
declare columnCursor cursor for
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, (SELECT  
       LocalizedLabelView_2.Label AS AttributeDisplayName
FROM    LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN
       AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN
       EntityView INNER JOIN 
       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON
       AttributeView.EntityId = EntityView.EntityId
WHERE  ( LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
AND LocalizedLabelView_1.LanguageId = '1033'
AND LocalizedLabelView_2.LanguageId = '1033'
AND AttributeView.Name=COLUMN_NAME
AND EntityView.Name IN ('account'))) as COLUMN_DisplayName ,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'YES' and TABLE_NAME='Account';
open columnCursor;
fetch next from columnCursor into @tableSchema, @tableName, @columnName,@columnDisplayName, @ColumnType;
while @@FETCH_STATUS = 0
begin
SET @sql1='declare columnCursor2 CURSOR  for 
select SU.BusinessUnitIdName as BU,COUNT(*) as Number  from Account as ACT inner join SystemUser as SU on SU.SystemUserId=ACT.OwnerId 
where ['ACT.'+'+@columnName+'] is not null group by SU.BusinessUnitIdName';
EXECUTE sp_executesql @sql1;
open columnCursor2;
fetch next from columnCursor2 into @BU,@calcul;
while @@FETCH_STATUS = 0
begin
insert into @TableBU select @columnName,@BU,@calcul;
fetch next from columnCursor2 into @BU,@calcul;
end
close columnCursor2;
deallocate columnCursor2;
set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
'] where [' + @columnName + '] is not null';
exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
insert into @tempTable select @tableSchema, @tableName, @columnName,@columnDisplayName, @ColumnType, @cnt;
fetch next from columnCursor into @tableSchema, @tableName, @columnName,@columnDisplayName,@ColumnType;
end;
close columnCursor;
deallocate columnCursor;
select TT.columnDisplayName as Nom_Business,TT.ColumnName as Nom_Technique,TT.ColumnType as Type, TT.NotNullCnt as Nombre_Renseigné,
(select COUNT(*)  from  Account)as Nombre_Totale,TB.BU,TB.CountBU from @tempTable as TT inner join @TableBU as TB on  TT.ColumnName =TB.ColumnName1 
where columnDisplayName is not null  group by TB.BU,TT.columnDisplayName , TT.ColumnName ,TB.ColumnName1,TT.ColumnType,TT.NotNullCnt,TB.CountBU
order by columnDisplayName;

After running this query I get the error :

Ambiguous column name 'CreatedByDsc'.    
A cursor with the name 'columnCursor2' does not exist.

I think this error is related to the variable @columnName which contains columns of Account table. The variable @columnName has to be considered as the columns from the Account table.

Thank you in advance for your help.

similar case https://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure — RoMEoMusTDiE, May 24 '17 at 23:32
and you have to remove the @columnname at the insert you don't need it — RoMEoMusTDiE, May 24 '17 at 23:33
What do you want to do exactly? Your desired output. You are using some variables that did not declared. — Vijunav Vastivch, May 25 '17 at 00:42
This in not all of the code that produces that error message. Please post all code or we are guessing — Nick.Mc, May 25 '17 at 01:28
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, May 25 '17 at 02:52
I don't see a column called `CreatedByDsc` in your SQL. I guess it's part of the dynamic SQL. Cursor columnCursor2 does not exist because you declared it inside `EXECUTE sp_executesql @sql1;`. It's not visible outside that. You don't need a cursor to load rows into another table like that. Just do one big insert/select inside @sql1 and execute it. — Nick.Mc, May 25 '17 at 12:49

RoMEoMusTDiE · Answer 1 · 2017-05-25T01:10:39.170

1

1) @sql1 as NVARCHAR
2) remove @columnname in the insert

declare @TableBU table 
  (ColumnName1 varchar(30), BU nvarchar(500), CountBU bigint );     

declare @sql1 as nvarchar(max)

set @columnName = 'field1'

    SET @sql1=N'declare columnCursor2 CURSOR  for 
    select SU.BusinessUnitIdName as BU,COUNT(*) as Number  from Account as ACT inner join SystemUser as SU on SU.SystemUserId=ACT.OwnerId 
    where ' + @columnName + ' is not null group by SU.BusinessUnitIdName'; 

    EXECUTE sp_executesql @sql1;

    open columnCursor2 

    fetch next from columnCursor2
     into @BU,@calcul

    while @@FETCH_STATUS = 0
    begin

    insert into @TableBU 
    select @BU,@calcul

    fetch next from columnCursor2 
    into @columnName,@BU,@calcul

    end

    close columnCursor2;
    deallocate columnCursor2;

edited May 25 '17 at 01:10

answered May 24 '17 at 23:30

RoMEoMusTDiE

4,739
1
17
26

I need the variable @columnName in the insert because I use it to join table BU with another table – tollamie May 24 '17 at 23:40
what's your @tableBU structure? – RoMEoMusTDiE May 24 '17 at 23:41
`declare @TableBU table ( ColumnName1 sysname, BU nvarchar(500), CountBU bigint ); ` – tollamie May 24 '17 at 23:43
is there any way to make it like `ACT.@columnName` ? – tollamie May 24 '17 at 23:49
should be ColumnName1 varchar(length) so you can insert the table name – RoMEoMusTDiE May 25 '17 at 01:05

How can I resolve an ambiguous column in where clause?

1 Answers1