Solved

row by row process executing a stored procedure

Posted on 2011-03-15
16
222 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 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 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

729 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