CODEWITHSUNDEEP
sqlsql-servert-sql
RMu
RMu

Reputation: 869

Need T-SQL help on looping

I have a ticketing database for which I'm writing a stored Procedure to alert each member of my team if they have any open tickets.

I have the select query to find the open tickets for the day. I have to group the tickets by user name and send an email to each individual. (For example, if I have 3 tickets open in my queue I would like to build a table and send 3 rows to me instead of three emails.)

Please guide me what are the functions/keywords I should be looking into in order to loop through each member of the team. I was looking into While loop but couldn't see how it will be helpful to me.

My idea so far is to create a table variable to populate necessary data.

In a Loop, 

Select blah blah WHERE user = @user

TSQL - Build a HTML table Send email to @user

I'm stuck how to loop through each user! Any help would be appreciated

Upvotes: 0

Views: 83

Answers (1)

Yashveer Singh
Yashveer Singh

Reputation: 1987

declare @user as table (username varchar(10)  , email varchar (20))
declare @ticket as table (username varchar(10)  , ticketnumber varchar (20))
insert into @user values('A','a.com')
insert into @user values('b','b.com')

insert into @ticket values('A','t1')
insert into @ticket values('A','t2')
insert into @ticket values('b','t3')

 declare @ticketNumber as varchar(100) 
 declare @userName as varchar(100)
 declare @user_cursor cursor
 declare @inner_cursor cursor
 declare @fetch_user_cursor int
 declare @fetch_inner_cursor int

 declare user_cursor cursor static local for 
 SELECT 
       username
  FROM @user

  /*loop through top level cursor*/
  open user_cursor
  fetch next from user_cursor into @userName
  select @fetch_user_cursor = @@FETCH_STATUS

  while @fetch_user_cursor = 0
  begin
    print @userName

     /*loop through second level cursor*/
     set @inner_cursor  = cursor static local for 
     select ticketnumber from @ticket where username = @userName


     open @inner_cursor
     fetch next from @inner_cursor into @ticketNumber
     set @fetch_inner_cursor = @@FETCH_STATUS
     while @fetch_inner_cursor = 0
     begin

      print @ticketNumber

      fetch next from @inner_cursor into @ticketNumber
      set @fetch_inner_cursor = @@FETCH_STATUS
     end
     close @inner_cursor
     deallocate @inner_cursor

     fetch next from user_cursor into @userName
     set @fetch_user_cursor = @@FETCH_STATUS


  end

Upvotes: 2

Related Questions