Stored Procedure returning # of var declared in the into... must match... selected columns

The following stored procedure receives this error:
Msg 16924, Level 16, State 1, Procedure c_UnPaidTickets, Line 99
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns

USE [Logic]
GO
/****** Object:  StoredProcedure [dbo].[c_UnPaidTickets]    Script Date: 04/10/2015 19:16:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: <03/14/14,>
-- Description:	<Send emails with links to tickets that are dated>
-- =============================================
ALTER PROCEDURE [dbo].[c_UnPaidTickets]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	Declare 
   @subject varchar(50),
   @body varchar(1000),
   @custid varchar (8), 
   @firstname varchar (100), 
   @lastname varchar(100), 
   @dtcreated varchar(50), 
   @sticketnumber varchar(50), 
   @ticketID varchar (12),
   @dbltotal varchar (12),
   @splate varchar (50), 
   @bhouseaccount varchar (8), 
   @recipientemail varchar (80), 
   @sbarcode varchar (50), 
   @monthlyaccountid varchar (50), 
   @monthlyaccountdescription  varchar (250),
   @monthlyamount varchar(12),
   @bodyformat varchar(12),
   @ticketagemin varchar (12)

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
   Declare unPaidTickets_Cursor cursor for
select datediff(minute,ti.dtupdated, getdate()) as 'Minutes Old',ti.dtCreated,ti.sTicket_Number,ti.dblTotal,ve.sPlate,ti.bHouseAccount, ti.Ticket_ID 
		from tickets as ti
		inner join vehicles as ve on ve.lVehicleID = ti.lVehicleID
		where datediff(day,dtupdated, getdate()) < 1 and datediff(minute,dtupdated, getdate()) > 15 and bPaid=0 and dbltotal>2 and SUSERNAME is not null
		and (bDeleted=0 or bDeleted is null)
		and NOT EXISTS(SELECT *
                    FROM tickets ti2
                   WHERE ti2.lVehicleID = ti.lVehicleID
                     AND CONVERT(varchar, ti2.dtupdated,112) = CONVERT(varchar, ti.dtupdated,112)
                     AND ti2.Ticket_ID <> ti.Ticket_ID)

OPEN UnPaidTickets_Cursor;
FETCH NEXT FROM UnPaidTickets_Cursor
INTO @ticketagemin, @dtcreated, @sticketnumber, @dbltotal, @splate, @bhouseaccount, @ticketID ;
 
declare @tmp_subject varchar(1000)
declare @tmp_body varchar(1000)

WHILE @@FETCH_STATUS = 0
   BEGIN
      
      set @tmp_subject = 'Unpaid Ticket: #'+@sticketnumber+' Plate: '+@splate+'at '+@dtcreated+''
                               
   set @tmp_body = '<table width="100%" border="1">
  <tr bgcolor="#66CCFF">
    <td width="297">Wash</td>
    <td width="289">Unpaid Tickets</td>
  </tr>
</table>
<p>Dear Cindy and Gregg,<br />
</p>
<p>Ticket #'+@sticketnumber+' was created '+@dtcreated+' with license plate '+@splate+' for the amount of '+@dbltotal+' but has not paid within '+@ticketagemin+' minutes <br />
  <br />
  Link to this ticket is </p>
<p>&nbsp;</p>
<table width="350" border="0">
  <tr>
    <td><img src="rwash.com/images/buggy.png" width="150" height="150" /></td>
  </tr>
  <tr>
    <td>Wash</td>
  </tr>
  <tr>
    <td>Avenue</td>
  </tr>
  <tr>
    <td>902</td>
  </tr>
  <tr>
    <td>-0727</td>
  </tr>
</table>
<p>&nbsp;</p>
<p>'
    --Sending Mail
    set @recipientemail='cindy@anyone.net'
    EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
          @subject = @tmp_subject,
          @body = @tmp_body,
          @body_format = 'HTML',
          @profile_name = 'Default';
             
      FETCH NEXT FROM UnPaidTickets_Cursor
      INTO @ticketagemin, @dtcreated, @sticketnumber, @dbltotal, @splate, @bhouseaccount;
   end;
CLOSE UnPaidTickets_Cursor;
DEALLOCATE UnPaidTickets_Cursor;

		
		END

Open in new window

4eggheadsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Your first FETCH is fine. Your last FETCH in the loop is missing the @ticket_ID as the last field.

A practice I developed decades ago was to only have one FETCH statement, as follows:
OPEN csr
WHILE 1 = 1
BEGIN
    FETCH  FROM csr INTO @Column1, @Column2 ... @ColumnN
    IF @@FETCH_STATUS <> 0 BREAK

    -- Do my stuff
END
CLOSE csr
DEALLOCATE csr

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Hi,
unPaidTickets_Cursor

is having 7 columns inside, while this line below

FETCH NEXT FROM UnPaidTickets_Cursor
      INTO @ticketagemin, @dtcreated, @sticketnumber, @dbltotal, @splate, @bhouseaccount;

Open in new window

is only having 6 variables. This is the reason of the error.
0
sarabhaiCommented:
Last fetch next statement missing the variable for column Ticket_ID


ALTER PROCEDURE [dbo].[c_UnPaidTickets]
      -- Add the parameters for the stored procedure here

AS
BEGIN
      Declare
   @subject varchar(50),
   @body varchar(1000),
   @custid varchar (8),
   @firstname varchar (100),
   @lastname varchar(100),
   @dtcreated varchar(50),
   @sticketnumber varchar(50),
   @ticketID varchar (12),
   @dbltotal varchar (12),
   @splate varchar (50),
   @bhouseaccount varchar (8),
   @recipientemail varchar (80),
   @sbarcode varchar (50),
   @monthlyaccountid varchar (50),
   @monthlyaccountdescription  varchar (250),
   @monthlyamount varchar(12),
   @bodyformat varchar(12),
   @ticketagemin varchar (12)

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
   Declare unPaidTickets_Cursor cursor for
select datediff(minute,ti.dtupdated, getdate()) as 'Minutes Old',ti.dtCreated,ti.sTicket_Number,ti.dblTotal,ve.sPlate,ti.bHouseAccount, ti.Ticket_ID
            from tickets as ti
            inner join vehicles as ve on ve.lVehicleID = ti.lVehicleID
            where datediff(day,dtupdated, getdate()) < 1 and datediff(minute,dtupdated, getdate()) > 15 and bPaid=0 and dbltotal>2 and SUSERNAME is not null
            and (bDeleted=0 or bDeleted is null)
            and NOT EXISTS(SELECT *
                    FROM tickets ti2
                   WHERE ti2.lVehicleID = ti.lVehicleID
                     AND CONVERT(varchar, ti2.dtupdated,112) = CONVERT(varchar, ti.dtupdated,112)
                     AND ti2.Ticket_ID <> ti.Ticket_ID)

OPEN UnPaidTickets_Cursor;
FETCH NEXT FROM UnPaidTickets_Cursor
INTO @ticketagemin, @dtcreated, @sticketnumber, @dbltotal, @splate, @bhouseaccount, @ticketID ;
 
declare @tmp_subject varchar(1000)
declare @tmp_body varchar(1000)

WHILE @@FETCH_STATUS = 0
   BEGIN
     
      set @tmp_subject = 'Unpaid Ticket: #'+@sticketnumber+' Plate: '+@splate+'at '+@dtcreated+''
                               
   set @tmp_body = '<table width="100%" border="1">
  <tr bgcolor="#66CCFF">
    <td width="297">Wash</td>
    <td width="289">Unpaid Tickets</td>
  </tr>
</table>
<p>Dear Cindy and Gregg,<br />
</p>
<p>Ticket #'+@sticketnumber+' was created '+@dtcreated+' with license plate '+@splate+' for the amount of '+@dbltotal+' but has not paid within '+@ticketagemin+' minutes <br />
  <br />
  Link to this ticket is </p>
<p>&nbsp;</p>
<table width="350" border="0">
  <tr>
    <td><img src="rwash.com/images/buggy.png" width="150" height="150" /></td>
  </tr>
  <tr>
    <td>Wash</td>
  </tr>
  <tr>
    <td>Avenue</td>
  </tr>
  <tr>
    <td>902</td>
  </tr>
  <tr>
    <td>-0727</td>
  </tr>
</table>
<p>&nbsp;</p>
<p>'
    --Sending Mail
    set @recipientemail='cindy@anyone.net'
    EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
          @subject = @tmp_subject,
          @body = @tmp_body,
          @body_format = 'HTML',
          @profile_name = 'Default';
             
      FETCH NEXT FROM UnPaidTickets_Cursor
      INTO @ticketagemin, @dtcreated, @sticketnumber, @dbltotal, @splate, @bhouseaccount, @ticketID ;
   end;
CLOSE UnPaidTickets_Cursor;
DEALLOCATE UnPaidTickets_Cursor;

            
END
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
4eggheads, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.