Return on result set from many tables using a cursor-CodePudding

I just practice with cursor. I want to Merage all table together. I hope you can understand my purpose. Can I return all result into 1 temporary table

DECLARE @dep NVARCHAR(max) = 'Computer Studies,Civil Engineering'
declare @tmpTable table (
    Table_Name NVARCHAR(max)
    ,Count int
)
declare cur CURSOR FAST_FORWARD FOR
    SELECT * FROM dbo.DEPARTMENTS  
OPEN cur
FETCH NEXT FROM cur

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT * FROM dbo.DEPARTMENTS WHERE DepartmentName = @dep
    fetch next from cur
END

CLOSE cur
DEALLOCATE cur

enter image description here

CodePudding user response：

You can use a temporary table to accumulate the results, eg

SET NOCOUNT ON
DECLARE @dep NVARCHAR(max) = 'Computer Studies,Civil Engineering'
declare @tmpTable table (
    Table_Name NVARCHAR(max)
    ,Count int
)
declare cur CURSOR FAST_FORWARD FOR
    SELECT * FROM dbo.DEPARTMENTS  

drop table if exists #results 
select * into #results from dbo.DEPARTMENTS where 1=0
OPEN cur
FETCH NEXT FROM cur

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #results
    SELECT * FROM dbo.DEPARTMENTS WHERE DepartmentName = @dep
    fetch next from cur
END

CLOSE cur
DEALLOCATE cur

select * from #results

CodePudding user response：

You don't need a cursor here. Don't practice with it, it's a waste of time. They are slow, inefficient, and nearly always unnecessary.

Instead use set-based logic, by placing your conditions in a table variable or Table Valued Parameter

DECLARE @deps TABLE (dep nvarchar(100) PRIMARY KEY);
INSERT @deps (dep)
VALUES ('Computer Studies'), ('Civil Engineering');

SELECT *
FROM dbo.DEPARTMENTS d
WHERE d.DepartmentName IN (
    SELECT t.dep
    FROM @deps t
);