Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Need to execute row by row process

Hi there,
I need to write a cursor that allows me to execute a stored procedure for each row found on my first query.
I want to do something like this:
My first query returns a set of values based on X criteria
SELECT NUMBER_KEY
FROM CD_MAIN
WHERE DATA_STATUS = 'Generated Work Order to E.S.'
and ENTERED_DATE <=
      CONVERT(VARCHAR(10), DATEADD(day, -180, GETDATE()), 101)

a set of X NUMBER_KEY are returned and i need to execute the following stored procedure:
usp_DAL_UpdateViolationStatus passing the NUMBER_KEY value, 'some text', and todays date in short format. whats the best practice to do this?
thanks,
COHFL
 
0
COHFL
Asked:
COHFL
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do this:
declare @t table (NUMBER_KEY int)
DECLARE @number_key int
declare @short_date date

set @short_date = getdate()

insert into @t 
 SELECT NUMBER_KEY
   FROM CD_MAIN
  WHERE DATA_STATUS = 'Generated Work Order to E.S.'
   and ENTERED_DATE <= CONVERT(VARCHAR(10), DATEADD(day, -180, GETDATE()), 101)

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

  if @@ROWCOUNT > 0
  BEGIN
    exec usp_DAL_UpdateViolationStatus @number_key, 'some text', @short_date 
  END

  DELETE @t WHERE NUMBER_KEY = @NUMBER_KEY
END

Open in new window

0
 
rajvjaCommented:
Store the first query result in a temp table. Temp table should have IDENTITY column.
Loop through temp table and call the stored procedure per each row.
0
 
COHFLAuthor Commented:
angelIII i get this error:
Msg 2715, Level 16, State 3, Line 2
Column or parameter #-2: Cannot find data type date.
Parameter '@short_date' has an invalid data type.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry.... datetime instead of date
0
 
COHFLAuthor Commented:
yeah i figure that one and another mistake but thats was my fault NUMBER_KEY is not a number so i changed to CHAR(10)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, not mistake as such, as I could not guess the data type from NUMBER_KEY to be not numerical :)
so, does it work for you?
0
 
COHFLAuthor Commented:
yes it did thanks once again angelII.

"not mistake as such, as I could not guess" => but thats was my fault (it is ok it was my fault i did not point out the data type but like i said i changed and it worked fine)

you rock!!!
0

Featured Post

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now