• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

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

0
4eggheads
Asked:
4eggheads
1 Solution
 
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
 
HuaMin ChenSystem AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now