Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

row by row process executing a stored procedure

Posted on 2011-03-15
16
Medium Priority
?
225 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

609 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