Must declare the scalar variable “@lookupname”

0
votes

I need to print if particular table values for all database in a server. I tried this below error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@lookupname".

Can anybody advise me as to the mistake I made?

declare @name varchar(50);
declare Countofprovider cursor 
    for 
SELECT name 
FROM master.dbo.sysdatabases where name like '%CPM_%'
     open Countofprovider   
        fetch next from Countofprovider into @name;
             while @@FETCH_STATUS = 0
                begin
                    --print 'Name : '+ @name+ '' 
                    declare @sql varchar(MAX);              
                    declare @lookupname varchar(100);
                    declare @lookupvalue varchar(100);
                    ---------------------------
                    SET @sql = 'select @lookupname=lookupname,@lookupvalue=lookupvalue from [AUS-CPMLDGDB01].'+@name+'.cpm.ApplicationLookup where lookupname = ''IsPCPLeakageEnabled'' and lookupvalue = ''TRUE'''
                    print 'Name : '+ @name+ ', lookupname : '+ @lookupname+ ', Loolupvalue : '+@lookupvalue+'' 
                    exec (@SQL)
                    ---------------------------
      fetch next from Countofprovider into @name
    end
 CLOSE Countofprovider;
 DEALLOCATE Countofprovider;
4
sqlsql-server
its working on sql server. – Mr. Bhosale
@Mr.Bhosale no it doesn't, it just appears to work since you don't have a database with a name like '%CPM_%', so @@FETCH_STATUS never has the value 0 and the code in the while is never executed. – HoneyBadger
i have added working code below . check it – Mr. Bhosale

4 Answers

1
votes

Have made some changes in your pl

declare @name varchar(50);
declare Countofprovider cursor 
    for 
SELECT name 
FROM master.dbo.sysdatabases where name like '%t%'
     open Countofprovider   
        fetch next from Countofprovider into @name;
             while @@FETCH_STATUS = 0
                begin
                    --print 'Name : '+ @name+ '' 
                    declare @sql nvarchar(MAX);              
                    declare @lookupname varchar(100);
                    declare @lookupvalue varchar(100);
                    ---------------------------
                    SET @sql = 'select @lookupname=lookupname,@lookupvalue=lookupvalue from [AUS-CPMLDGDB01].'+@name+'.cpm.ApplicationLookup where lookupname = ''IsPCPLeakageEnabled'' and lookupvalue = ''TRUE'''

                  exec sp_executesql 
                    @sql, 
                    N'@lookupname varchar(100) out, @lookupvalue varchar(100) out', 
                    @lookupname out, 
                    @lookupvalue out

                    print 'Name : '+ @name+ ', lookupname : '+ @lookupname+ ', Loolupvalue : '+@lookupvalue+'' 

                    ---------------------------
      fetch next from Countofprovider into @name
    end
 CLOSE Countofprovider;
 DEALLOCATE Countofprovider;

Dynamic query must be stored in nvarchar variable 

declare @sql nvarchar(MAX);

and 

 exec sp_executesql 
                    @sql, 
                    N'@lookupname varchar(100) out, @lookupvalue varchar(100) out', 
                    @lookupname out, 
                    @lookupvalue out
0
votes

Since you're using outer variables in your dynamic sql query, you have to explicitly pass this variables into dynamic sql execution.

So change 

exec(@SQL)

to 

exec sp_executesql 
   @sql, 
   N'@lookupname varchar(100) out, @lookupvalue varchar(100) out', 
   @lookupname out, 
   @lookupvalue out
0
votes

You have to use parameters in your query:

exec sp_executesql @sql, N'@lookupname varchar(100) OUT, @lookupvalue varchar(100) OUT', @lookupname = @lookupname OUT, @lookupvalue = @lookupvalue OUT
0
votes

try below code.

            declare @name varchar(50);
            declare Countofprovider cursor 
                for 
            SELECT name 
            FROM master.dbo.sysdatabases where name like '%CPM_%'
                 open Countofprovider   
                    fetch next from Countofprovider into @name;
                         while @@FETCH_STATUS = 0
                            begin
                                --print 'Name : '+ @name+ '' 
                                declare @sql nvarchar(MAX);              
                                declare @lookupname varchar(100);
                                declare @lookupvalue varchar(100);
                                ---------------------------
                                SET @sql = 'select @lookupname=lookupname,@lookupvalue=lookupvalue from [AUS-CPMLDGDB01].'+@name+'.cpm.ApplicationLookup where lookupname = ''IsPCPLeakageEnabled'' and lookupvalue = ''TRUE'''
                                print 'Name : '+ @name+ ', lookupname : '+ @lookupname+ ', Loolupvalue : '+@lookupvalue+'' 
                               exec sp_executesql @sql, N'@lookupname varchar(100) OUT, @lookupvalue varchar(100) OUT', @lookupname = @lookupname OUT, @lookupvalue = @lookupvalue OUT

                                ---------------------------
                  fetch next from Countofprovider into @name
                end
             CLOSE Countofprovider;
             DEALLOCATE Countofprovider;