How execute sql within a cursor

KevinH2 21 Reputation points
2022-06-22T20:06:31.083+00:00

I can't get the results of this cursor to execute

DECLARE @myVar varchar(MAX)
DECLARE @alenzi varchar(MAX)
DECLARE myCursor CURSOR FOR
SELECT name from master.sys.databases

OPEN myCursor
FETCH NEXT FROM myCursor INTO @myVar

WHILE @@Fetch _STATUS = 0
BEGIN
SET @alenzi = 'SELECT name FROM '+@myVar+'.sys.assemblies'
EXEC sp_executesql @alenzi
FETCH NEXT FROM myCursor INTO @myVar
END

CLOSE myCursor
DEALLOCATE myCursor

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,101 Reputation points
    2022-06-22T20:31:57.45+00:00

    DECLARE @alenzi varchar(MAX)

    Specify nvarchar(MAX) for the sp_executesql parameter type:

    DECLARE @sql nvarchar(MAX);  
    

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,706 Reputation points
    2022-06-23T12:55:01.457+00:00

    In your case I prefer not to use a cursor and just create a string with all results

    DECLARE @sql nvarchar(MAX)  
      
    SET @sql = (  
    SELECT   
    	STRING_AGG(CONCAT('SELECT ''',name,''' as [DBName], [name] as [AssemblyName] FROM [',name,'].sys.assemblies') , CHAR(10)+'UNION ALL'+CHAR(10)) WITHIN GROUP (ORDER BY [name])  
    from master.sys.databases  
    );  
      
    EXEC sp_executesql @sql  
    
    1 person found this answer helpful.

  2. Naomi 7,361 Reputation points
    2022-06-22T20:28:27.57+00:00

    Try changing this line to
    SET @alenzi = 'SELECT name FROM '+QUOTENAME(@myVar) +'.sys.assemblies' ;

    also you may probably want to exclude system databases ?

    Are you getting an error with your code?