How to create stored procedure with print from 2 different tables?

Question

I have 3 tables in a database, 1 been a yoga class, another been the members and an intersection between them, I want to create a stored procedure that will print the details of a specified class and each member's name and contact number assigned to the class. The reports output should have the same format as:

YOGA CLASS REPORT:
___________________

Class ID: 1112
Week day:   Monday    [Time: 07:00:00]
Studio Number: 1

No.  Member Name         Contact Number
________________________________________
1    John Doe            +26 83 562 3953

So far I have:

CREATE PROCEDURE sp_Report  
@classID SMALLINT  
AS  
  IF NOT EXISTS(SELECT * FROM class WHERE classID = @classID)      
  BEGIN  
     RAISERROR ('classID does not exist',16,1)  
     RETURN  
  END

  DECLARE @classWeekday VARCHAR(15)  
  DECLARE @classTime TIME  
  DECLARE @classStudioNo TINYINT

  SELECT @classWeekday = class.classWeekday, @classTime = class.classTime, 
         @classStudioNo = class.classStudioNo  
  FROM class  
  WHERE @classID = classID

  PRINT 'YOGA CLASS REPORT'  
  PRINT 'Class code:' +CAST(@classID AS VARCHAR)  
  PRINT 'Week day:' +@classWeekday +'[Time:' + CAST(@classTime AS VARCHAR)+ ']'   
  PRINT 'Studio Number' +CAST(@classStudioNo AS VARCHAR)

How do I now print all the members that attend a specific class ? any help will greatly appreciated thanks.

score 0 · Answer 1 · answered Jul 19 '12 at 16:30

0

Have you tried to use a Cursor?

PRINT 'YOGA CLASS REPORT' 

DECLARE cClasses CURSOR FOR
SELECT classWeekday, classTime, classStudioNo  
FROM class  
WHERE classID = @classID

OPEN cClasses
FETCH NEXT FROM cClasses INTO @classWeekday, @classTime, @classStudioNo
WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT 'Class code:' +CAST(@classID AS VARCHAR)  
    PRINT 'Week day:' +@classWeekday +'[Time:' + CAST(@classTime AS VARCHAR)+ ']'   
    PRINT 'Studio Number' +CAST(@classStudioNo AS VARCHAR)

FETCH NEXT FROM cClasses INTO @classWeekday, @classTime, @classStudioNo
END
CLOSE cClasses
DEALLOCATE cClasses

Here you have oficial documentation in msdn DECLARE CURSOR SQL SERVER

answered Jul 19 '12 at 16:30

Elwi

687
1
5
15

1

You're on the right track, but he's asking how to print the students, not the classes. – Bert Jul 19 '12 at 16:34
That's right! maybe I don't read the question very well. Anyway, he could use a CURSOR to do the job. Thank's for the comment @BertEvans – Elwi Jul 19 '12 at 16:51
I've never used cursors before,I'm pretty new to sql. I'm asking how do I print the **memberName**, **memberTelephone** from the **member** table that attend the specific class that is queried, keep in mind that not all members attend every class. I would assume that it will have to be in a WHILE loop and iterate and JOIN to check which members attend classID N. I need help as I'm new to sql and dont know where to go from what I have already. Thanks for replying @Elwi – Yi-Nain Chen Jul 19 '12 at 17:13
what are the field names in the two tables that intersect? – whytheq Jul 19 '12 at 17:53

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

Something like this will give you the people attending each class...

SELECT 
     c.MemeberID
     , m.MemberName
     , m.ContactNumber
FROM 
     class c
     INNER JOIN members m ON
         c.MemberID = m.MemberID 
WHERE c.classID = @classID

looking in this Stack Overflow question I'm wondering why you are actually using PRINT ?

edited May 23 '17 at 12:12

Community

1
1

answered Jul 19 '12 at 17:57

whytheq

34,466
65
172
267

Its for an exercise in class where the stored procedure must be created like this. – Yi-Nain Chen Jul 19 '12 at 18:35

score 0 · Answer 3 · edited Jan 18 '21 at 12:32

As the others have mentioned, I'm curious why you would use print for this, but given that as a constraint, you would have to cursor through the join. Below is some sample code and a working example.

Edit: I just want to add that, typically, when working with SQL, you will want to avoid using cursors. SQL is a set based language, and cursors are procedural. So, while I have given an example using a cursor in order to meet the constraint of having to print each result, I would not do this in any production environment.

declare @class table (classID int, className nvarchar(100))
insert @class values (1,'TSQL'),(2,'SQL Server')
declare @members table (memberID int, memberName nvarchar(100), contactNumber nvarchar(100))
insert @members values (1, 'John Doe', '+26 83 562 3953'), (2, 'Bert Evans','+26 83 562 3954')
declare @classmembers table (classID int, memberID int)
insert @classmembers values (1,1),(1,2)

declare attendees cursor for 
    select rsMembers.memberID, memberName, contactNumber
    from @members rsMembers
    inner join @classmembers rsClassMembers on rsClassMembers.memberID = rsMembers.memberID
    where rsClassMembers.classID = 1
    
open attendees
declare @number int, @name nvarchar(100), @contact nvarchar(100)
fetch next from attendees into @number, @name, @contact
while @@FETCH_STATUS = 0
begin

    print cast(@number as nvarchar(100)) + ' ' + @name + ' ' + @contact
    fetch next from attendees into @number, @name, @contact
    
end
close attendees
deallocate attendees

Working example.

Thanks for the help, I'm actually currently still learning sql, and the reason why I am using print is that its for a class exercise and the instructors teaching me sql have specified that for this stored procedure a print must be used and the format must be as I posted (see above). — Yi-Nain Chen, Jul 19 '12 at 18:29
@Yi-NainChen NP, I figured you were constrained in some way. — Bert, Jul 19 '12 at 18:31

How to create stored procedure with print from 2 different tables?

3 Answers3