?
Solved

row by row process executing a stored procedure

Posted on 2011-03-15
16
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 143

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 143

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 143

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
 

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

Expert Comment

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

at the end of the script :)
0
 
LVL 143

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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