Link to home
Start Free TrialLog in
Avatar of Denis Orozco
Denis OrozcoFlag for United States of America

asked on

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
 
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Denis Orozco

ASKER

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.
sorry.... datetime instead of date
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)
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?
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!!!