row by row process executing a stored procedure

where/how do you declare @t ?

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

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?

angelIII,
@t  is declare on the first line of code:
declare @t table (NUMBER_KEY CHAR(10))

Hi, It is miss match in no of columns in table and values supplied in.

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

Select allOpen in new window

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

Select allOpen in new window

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?

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

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

Select allOpen in new window

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 

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

Select allOpen 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 ...

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

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?
 

you simply add:
select * from @t  

at the end of the script :)

>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

Select allOpen in new window

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