Procedure with cursor has missing output

I am working on an SQL Server procedure that I would like to have print the department name and students name Similar to the attached snippet

The formatting should look like the attached sample snippet and there are two columns involved. students and department tables.

The problem is that the results are incomplete/seems to be getting cut off below is the code that I am using

use testDB 
go
CREATE PROCEDURE pro_depart11
AS
SET NOCOUNT ON;    
declare @depcount as int;
declare @val as int;
declare @studentN as NVARCHAR(500);
declare @dname varchar(500);

set @val = 1;
set @depcount = (select count(*) from DEPARTMENT);
PRINT '************procedure pro_department report************'

While (@val<=@depcount)
begin
  set @dname = (select DNAME from DEPARTMENT where DEPTid=@val)
  PRINT '******************'+ @dname + '*************************'
  declare getnam_cursor cursor for

  SELECT sname FROM STUDENT s inner join DEPARTMENT d on s.DEPT_ID = d.DEPTID WHERE d.DEPTID = @val 
  group by sname order by sname asc;

  open getn_cursor

  FETCH NEXT FROM getnam_cursor into @studentN
  WHILE @@FETCH_STATUS = 0    
  BEGIN
    Print  CAST(@studentN as varchar (500))
    set @val = @val + 1;
    FETCH NEXT FROM getnam_cursor into @studentN
  end

  close getnam_cursor;
  deallocate getnam_cursor;
end

To run the script exec pro_depart11;

I’ve also included a snippet of the tables. students then department tables

The third snippet is of the results I am getting. I am not sure where I am going wrong with the code or how to best fix it, any help is greatly appreciated.

Below is the DLL/DML codes involved for the table/inserts

    CREATE TABLE dbo.STUDENT
     (snum      int,
      sname     nvarchar(12),
      MAJOR     nvarchar(12),
      DEPT_ID   int,
      slevel    nvarchar(12),
      AGE       int,
      CONSTRAINT STUDENT_SNUM_pk PRIMARY KEY (SNUM));

    CREATE TABLE dbo.DEPARTMENT
      (DEPTid   int,
       DNAME    nvarchar(100),
       LOCATION nvarchar(100),

insert into dbo.STUDENT values (0418,'S.Jack','Math',2,'SO',17);
insert into dbo.STUDENT values (0671,'A.Smith','English',2,'FR',20);
insert into dbo.STUDENT values (1234,'T.Banks','ME',3,'SR',19);
insert into dbo.STUDENT values (3726,'M.Lee','Physics',4,'SO',21);
insert into dbo.STUDENT values (4829,'J.Bale','Chemistry',5,'JR',22);
insert into dbo.STUDENT values (5765,'L.Lim','CS',1,'SR',19);
insert into dbo.STUDENT values (0019,'D.Sharon','History', 4,'FR',20);
insert into dbo.STUDENT values (7357,'G.Johnson','Math', 4,'JR',19);
insert into dbo.STUDENT values (8016,'E.Cho','History',2,'JR',19);


insert into dbo.DEPARTMENT values (1,'Computer Sciences','West Lafayette');
insert into dbo.DEPARTMENT values (2,'Management','West Lafayette');
insert into dbo.DEPARTMENT values (3,'Medical Education','Purdue Calumet');
insert into dbo.DEPARTMENT values (4,'Education','Purdue North Central');
insert into dbo.DEPARTMENT values (5,'Pharmacal Sciences','Indianapolis');

Answer

First key point, you don’t need a nested loop, order by department and have a condition for when the name changes.

declare @StudentName varchar(32), @DeptName varchar(32), @OldDeptName varchar(32) = '';

declare getnam_cursor cursor for
select s.sname StudentName, d.dname DeptName
from #Student s
inner join #Department d on d.deptid = S.Dept_id
order by d.dname, s.sname asc;

open getnam_cursor;
while 1 = 1 begin    
  fetch next from getnam_cursor into @StudentName, @DeptName;
  if @@fetch_status != 0 break;
  if @OldDeptName <> @DeptName begin
    -- Ensure the line is the same length regardless of the length of the name
    print(replicate('*',18) + ' ' + @DeptName + ' ' + replicate('*',32-len(@DeptName)));
    set @OldDeptName = @DeptName;
  end;

  print(@StudentName);
end;

close getnam_cursor;
deallocate getnam_cursor;

Returns:

****************** Computer Sciences ***************
L.Lim
****************** Education ***********************
D.Sharon
G.Johnson
M.Lee
****************** Management **********************
A.Smith
E.Cho
S.Jack
****************** Medical Education ***************
T.Banks
****************** Pharmacal Sciences **************
J.Bale

Other points to note:

• Use good layout and whitespace and you (and others will be able to follow your code easily).
• Use consistent casing for your code and column names. Mixing it up just makes it hard to read.
• Don’t prefix column names when they are unique by table e.g. dname, sname – just use Name.
• Always explicitly list the columns you are inserting into
• Don’t assume your department ids are sequential… in real life they might not be.
• You don’t need a group by unless you are aggregating (which you aren’t).