Solved

row by row process executing a stored procedure

Posted on 2011-03-15
16
213 Views
Last Modified: 2012-05-11
HI there,
I getting an error while i execute the code below.
"Msg 213, Level 16, State 5, Line 4
Insert Error: Column name or number of supplied values does not match table definition."

I'm not sure what the error is pointing me to. another set of eyes might help.

declare @t table (NUMBER_KEY CHAR(10))
DECLARE @number_key int

insert into @t 
	SELECT  [p].PARCEL_NO as [PROPERTYID], [m].NUMBER_KEY , -- [PERMIT_NUM]
			SUBSTRING([p].PARCEL_NO,2,1) + SUBSTRING([p].PARCEL_NO,4,3) + '-' + 
			SUBSTRING([p].PARCEL_NO,7,2) + '-' + SUBSTRING([p].PARCEL_NO,9,4) AS [FOLIO], 
			LTRIM(RTRIM([m].SUB_TYPE)) + ' ' + LTRIM(RTRIM([m].SUB_TYPE_DESC)) + ' ' + 
			LTRIM(RTRIM([p].PARCEL_NO)) AS [DESCRIPTION], [m].ENTERED_DATE, [m].DATE_K as [DATE_CC], 
			[m].DATE_J as [DATE_CO], [m].DATE_H as [DATE_TC], [m].DATE_F as DATE_CLOSED, [m].VAL_CUST, [m].VAL_CALCULATED, [m].VAL_REVIEWER, 
			[m].VAL_OVERRIDE_FLAG
    FROM CD_Main [m] LEFT OUTER JOIN CD_PARCEL [p] ON [m].NUMBER_KEY = [p].NUMBER_KEY 
    WHERE 
			([m].DATE_F BETWEEN '01/01/2009' AND '02/28/2011')
			AND  [m].DATA_LEVEL = 'P' 
			AND TYPE_DEPT='BUILDING'  
			AND DATA_STATUS <> 'CANCELLED'  
			AND [m].SUB_TYPE NOT IN ('BELLSOU','FLAPOWE','ROWRIG','FLORIDA')
			AND [m].COMP_TYPE='STRUCTUR'
			AND left([p].PARCEL_NO,1) like '5%' -- removes bogus folios

WHILE @@ROWCOUNT> 0
BEGIN
  SELECT TOP 1 @NUMBER_KEY = NUMBER_KEY FROM @t

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY 
  END

  DELETE @t WHERE NUMBER_KEY = @NUMBER_KEY
END

Open in new window

0
Comment
Question by:COHFL
  • 8
  • 7
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138086
where/how do you declare @t ?

presumably, you have more/less columns in @t declared than your SELECT statement for the INSERT.
0
 

Author Comment

by:COHFL
ID: 35138097
i guess i'm missing some information here. what i need to do is to include the result form the line
 exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY  into my original query it returns a Column named Last_Inspection. how can approach this task?
0
 

Author Comment

by:COHFL
ID: 35138120
angelIII,
@t  is declare on the first line of code:
declare @t table (NUMBER_KEY CHAR(10))
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35138231
Hi, It is miss match in no of columns in table and values supplied in.
0
 

Author Comment

by:COHFL
ID: 35138402
ok i see what you guys were saying and it makes total sense.

NO w i have modified my procedure as shown below. now how do i include the result of:
exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY  into my original query???


declare @t table (
PROPERTYID CHAR(20),
PERMIT_NUM CHAR(10),
FOLIO CHAR(20),
DESCRIPTION CHAR(100),
ENTERED_DATE datetime,
DATE_CC datetime,
DATE_CO datetime,
DATE_TC datetime,
DATE_CLOSED datetime,
VAL_CUST money,
VAL_CALCULATED money,
VAL_REVIEWER money,
VAL_OVERRIDE_FLAG CHAR(20),
LAST_INSPECTION datetime
)
DECLARE @number_key CHAR(10)

insert into @t 
	SELECT  [p].PARCEL_NO as [PROPERTYID], [m].NUMBER_KEY as [PERMIT_NUM],
			SUBSTRING([p].PARCEL_NO,2,1) + SUBSTRING([p].PARCEL_NO,4,3) + '-' + 
			SUBSTRING([p].PARCEL_NO,7,2) + '-' + SUBSTRING([p].PARCEL_NO,9,4) AS [FOLIO], 
			LTRIM(RTRIM([m].SUB_TYPE)) + ' ' + LTRIM(RTRIM([m].SUB_TYPE_DESC)) + ' ' + 
			LTRIM(RTRIM([p].PARCEL_NO)) AS [DESCRIPTION], [m].ENTERED_DATE, [m].DATE_K as [DATE_CC], 
			[m].DATE_J as [DATE_CO], [m].DATE_H as [DATE_TC], [m].DATE_F as DATE_CLOSED, [m].VAL_CUST, [m].VAL_CALCULATED, [m].VAL_REVIEWER, 
			[m].VAL_OVERRIDE_FLAG, LAST_INSPECTION=''
    FROM CD_Main [m] LEFT OUTER JOIN CD_PARCEL [p] ON [m].NUMBER_KEY = [p].NUMBER_KEY 
    WHERE 
			([m].DATE_F BETWEEN '01/01/2009' AND '02/28/2011')
			AND  [m].DATA_LEVEL = 'P' 
			AND TYPE_DEPT='BUILDING'  
			AND DATA_STATUS <> 'CANCELLED'  
			AND [m].SUB_TYPE NOT IN ('BELLSOU','FLAPOWE','ROWRIG','FLORIDA')
			AND [m].COMP_TYPE='STRUCTUR'
			AND left([p].PARCEL_NO,1) like '5%' -- removes bogus folios

WHILE @@ROWCOUNT> 0
BEGIN
  SELECT TOP 1 @NUMBER_KEY = PERMIT_NUM FROM @t

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY 
  END

  DELETE @t WHERE PERMIT_NUM =  @NUMBER_KEY
END

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138517
that shall run, but why insert all those rows of data, if all you need is the @number_key field ? :)
declare @t table (
--PROPERTYID CHAR(20),
PERMIT_NUM CHAR(10),
/*
FOLIO CHAR(20),
DESCRIPTION CHAR(100),
ENTERED_DATE datetime,
DATE_CC datetime,
DATE_CO datetime,
DATE_TC datetime,
DATE_CLOSED datetime,
VAL_CUST money,
VAL_CALCULATED money,
VAL_REVIEWER money,
VAL_OVERRIDE_FLAG CHAR(20),
LAST_INSPECTION datetime
*/
)
DECLARE @number_key CHAR(10)

insert into @t ( PERMIT_NUM )
        SELECT  --[p].PARCEL_NO as [PROPERTYID],
  [m].NUMBER_KEY as [PERMIT_NUM]
 (* , SUBSTRING([p].PARCEL_NO,2,1) + SUBSTRING([p].PARCEL_NO,4,3) + '-' + 
                        SUBSTRING([p].PARCEL_NO,7,2) + '-' + SUBSTRING([p].PARCEL_NO,9,4) AS [FOLIO], 
                        LTRIM(RTRIM([m].SUB_TYPE)) + ' ' + LTRIM(RTRIM([m].SUB_TYPE_DESC)) + ' ' + 
                        LTRIM(RTRIM([p].PARCEL_NO)) AS [DESCRIPTION], [m].ENTERED_DATE, [m].DATE_K as [DATE_CC], 
                        [m].DATE_J as [DATE_CO], [m].DATE_H as [DATE_TC], [m].DATE_F as DATE_CLOSED, [m].VAL_CUST, [m].VAL_CALCULATED, [m].VAL_REVIEWER, 
                        [m].VAL_OVERRIDE_FLAG, LAST_INSPECTION=''
    */ 
    FROM CD_Main [m] LEFT OUTER JOIN CD_PARCEL [p] ON [m].NUMBER_KEY = [p].NUMBER_KEY 
    WHERE 
                        ([m].DATE_F BETWEEN '01/01/2009' AND '02/28/2011')
                        AND  [m].DATA_LEVEL = 'P' 
                        AND TYPE_DEPT='BUILDING'  
                        AND DATA_STATUS <> 'CANCELLED'  
                        AND [m].SUB_TYPE NOT IN ('BELLSOU','FLAPOWE','ROWRIG','FLORIDA')
                        AND [m].COMP_TYPE='STRUCTUR'
                        AND left([p].PARCEL_NO,1) like '5%' -- removes bogus folios

WHILE @@ROWCOUNT> 0
BEGIN
  SELECT TOP 1 @NUMBER_KEY = PERMIT_NUM FROM @t

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY 
  END

  DELETE @t WHERE PERMIT_NUM =  @NUMBER_KEY
END

Open in new window

0
 

Author Comment

by:COHFL
ID: 35138579
ok lets pretend i dont need all rows of data =)
Now the stored procedure exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY does not update it just select data how can i include whatever it gets return to the temp table? it returns one column "Last_Inspection"
How can i do this?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138626
if the procedure returns the info as OUTPUT parameter, you could then run an UPDATE..
however, that will not match (yet) with the current logic to "delete" the rows from the temp table, but we can change that on doing an update of the returned column value, and checking for the column being NULL in the SELECT TOP 1 for the loop query ...
hope this was clear as mud ...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:COHFL
ID: 35138675
is muddy over here already!

here is the SQL Statement for my procedure. Now how can i change it to get the result i want? and yeah delete the row from @t is not what i want
ALTER PROCEDURE [dbo].[usp_ExpiredLetterLastInsp_Check] 
(@PERMIT varchar (20))
as


SELECT  MAX(I.LAST_INSP)as LAST_INSPECTION FROM CD_MAIN M inner join uw_ExpiredPErmits_LastInsp I
ON M.NUMBER_KEY = I.NUMBER_KEY
WHERE M.DEV_NUMBER = @PERMIT or M.NUMBER_KEY = @PERMIT

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138743
ok, let's presume you modify the proc like this:

ALTER PROCEDURE [dbo].[usp_ExpiredLetterLastInsp_Check] 
(@PERMIT varchar (20)
, @LAST_INSPECTION datetime OUTPUT --- I presume that data type ...
)
as


SELECT  @LAST_INSPECTION = MAX(I.LAST_INSP) 
  FROM CD_MAIN M 
  JOIN uw_ExpiredPErmits_LastInsp I
ON M.NUMBER_KEY = I.NUMBER_KEY
WHERE M.DEV_NUMBER = @PERMIT 
    or M.NUMBER_KEY = @PERMIT 

Open in new window


from there, you use that:
 declare @t table (
--PROPERTYID CHAR(20),
PERMIT_NUM CHAR(10),
/*
FOLIO CHAR(20),
DESCRIPTION CHAR(100),
ENTERED_DATE datetime,
DATE_CC datetime,
DATE_CO datetime,
DATE_TC datetime,
DATE_CLOSED datetime,
VAL_CUST money,
VAL_CALCULATED money,
VAL_REVIEWER money,
VAL_OVERRIDE_FLAG CHAR(20)
*/
, LAST_INSPECTION datetime
)
DECLARE @number_key CHAR(10)

insert into @t ( PERMIT_NUM )
        SELECT  --[p].PARCEL_NO as [PROPERTYID],
  [m].NUMBER_KEY as [PERMIT_NUM]
 (* , SUBSTRING([p].PARCEL_NO,2,1) + SUBSTRING([p].PARCEL_NO,4,3) + '-' + 
                        SUBSTRING([p].PARCEL_NO,7,2) + '-' + SUBSTRING([p].PARCEL_NO,9,4) AS [FOLIO], 
                        LTRIM(RTRIM([m].SUB_TYPE)) + ' ' + LTRIM(RTRIM([m].SUB_TYPE_DESC)) + ' ' + 
                        LTRIM(RTRIM([p].PARCEL_NO)) AS [DESCRIPTION], [m].ENTERED_DATE, [m].DATE_K as [DATE_CC], 
                        [m].DATE_J as [DATE_CO], [m].DATE_H as [DATE_TC], [m].DATE_F as DATE_CLOSED, [m].VAL_CUST, [m].VAL_CALCULATED, [m].VAL_REVIEWER, 
                        [m].VAL_OVERRIDE_FLAG, LAST_INSPECTION=''
    */ 
    FROM CD_Main [m] LEFT OUTER JOIN CD_PARCEL [p] ON [m].NUMBER_KEY = [p].NUMBER_KEY 
    WHERE 
                        ([m].DATE_F BETWEEN '01/01/2009' AND '02/28/2011')
                        AND  [m].DATA_LEVEL = 'P' 
                        AND TYPE_DEPT='BUILDING'  
                        AND DATA_STATUS <> 'CANCELLED'  
                        AND [m].SUB_TYPE NOT IN ('BELLSOU','FLAPOWE','ROWRIG','FLORIDA')
                        AND [m].COMP_TYPE='STRUCTUR'
                        AND left([p].PARCEL_NO,1) like '5%' -- removes bogus folios

WHILE @@ROWCOUNT> 0
BEGIN
  SELECT TOP 1 @NUMBER_KEY = PERMIT_NUM FROM @t WHERE LAST_INSPECTION IS NULL

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY , @LAST_INSPECTION  OUTPUT
    update @t  
        set LAST_INSPECTION = isnull(@LAST_INSPECTION, getdate())
     WHERE PERMIT_NUM =  @NUMBER_KEY
  END

END

Open in new window


if last_inspection might be NULL, we need another column in the @t table to indicate that we have processed the record ...
0
 

Author Comment

by:COHFL
ID: 35138817
i get an error on:
exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY , @LAST_INSPECTION  OUTPUT

Msg 137, Level 15, State 2, Line 48
Must declare the variable '@LAST_INSPECTION'.
Msg 137, Level 15, State 2, Line 50
Must declare the variable '@LAST_INSPECTION'.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35138911
sorry, indeed:
DECLARE @last_inspection DATETIME

 declare @t table (
--PROPERTYID CHAR(20),
PERMIT_NUM CHAR(10),
/*
FOLIO CHAR(20),
DESCRIPTION CHAR(100),
ENTERED_DATE datetime,
DATE_CC datetime,
DATE_CO datetime,
DATE_TC datetime,
DATE_CLOSED datetime,
VAL_CUST money,
VAL_CALCULATED money,
VAL_REVIEWER money,
VAL_OVERRIDE_FLAG CHAR(20)
*/
, LAST_INSPECTION datetime
)
DECLARE @number_key CHAR(10)

insert into @t ( PERMIT_NUM )
        SELECT  --[p].PARCEL_NO as [PROPERTYID],
  [m].NUMBER_KEY as [PERMIT_NUM]
 (* , SUBSTRING([p].PARCEL_NO,2,1) + SUBSTRING([p].PARCEL_NO,4,3) + '-' + 
                        SUBSTRING([p].PARCEL_NO,7,2) + '-' + SUBSTRING([p].PARCEL_NO,9,4) AS [FOLIO], 
                        LTRIM(RTRIM([m].SUB_TYPE)) + ' ' + LTRIM(RTRIM([m].SUB_TYPE_DESC)) + ' ' + 
                        LTRIM(RTRIM([p].PARCEL_NO)) AS [DESCRIPTION], [m].ENTERED_DATE, [m].DATE_K as [DATE_CC], 
                        [m].DATE_J as [DATE_CO], [m].DATE_H as [DATE_TC], [m].DATE_F as DATE_CLOSED, [m].VAL_CUST, [m].VAL_CALCULATED, [m].VAL_REVIEWER, 
                        [m].VAL_OVERRIDE_FLAG, LAST_INSPECTION=''
    */ 
    FROM CD_Main [m] LEFT OUTER JOIN CD_PARCEL [p] ON [m].NUMBER_KEY = [p].NUMBER_KEY 
    WHERE 
                        ([m].DATE_F BETWEEN '01/01/2009' AND '02/28/2011')
                        AND  [m].DATA_LEVEL = 'P' 
                        AND TYPE_DEPT='BUILDING'  
                        AND DATA_STATUS <> 'CANCELLED'  
                        AND [m].SUB_TYPE NOT IN ('BELLSOU','FLAPOWE','ROWRIG','FLORIDA')
                        AND [m].COMP_TYPE='STRUCTUR'
                        AND left([p].PARCEL_NO,1) like '5%' -- removes bogus folios

WHILE @@ROWCOUNT> 0
BEGIN
  SELECT TOP 1 @NUMBER_KEY = PERMIT_NUM FROM @t WHERE LAST_INSPECTION IS NULL

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_ExpiredLetterLastInsp_Check @NUMBER_KEY , @LAST_INSPECTION  OUTPUT
    update @t  
        set LAST_INSPECTION = isnull(@LAST_INSPECTION, getdate())
     WHERE PERMIT_NUM =  @NUMBER_KEY
  END

END

Open in new window

0
 

Author Comment

by:COHFL
ID: 35138946
now i done see anything on my result set i only see messages like this:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
how can i see the result set as a normal select statement?
 
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138963
you simply add:
select * from @t  

at the end of the script :)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138975
>Warning: Null value is eliminated by an aggregate or other SET operation.
this is raised in your procedure, btw
ALTER PROCEDURE [dbo].[usp_ExpiredLetterLastInsp_Check] 
(@PERMIT varchar (20)
, @LAST_INSPECTION datetime OUTPUT --- I presume that data type ...
)
as
SET  @LAST_INSPECTION = NULL

SELECT TOP 1 @LAST_INSPECTION = I.LAST_INSP
  FROM CD_MAIN M 
  JOIN uw_ExpiredPErmits_LastInsp I
ON M.NUMBER_KEY = I.NUMBER_KEY
WHERE M.DEV_NUMBER = @PERMIT 
    or M.NUMBER_KEY = @PERMIT 
ORDER BY = I.LAST_INSP DESC

Open in new window

0
 

Author Comment

by:COHFL
ID: 35139027
Null value is eliminated by an aggregate or other SET operation. meaning that there is not record so is removed from the @t table?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now