stored proc for auto update
I was hoping to make an update statement that took some parameters and would auto update multiple records.
So lets say I have this update for one record.
Update table a
set tabla.Value = 'PRE100000'
where id = '12345'
I would want to pass a start seed value and an end seed value.
This would essentially create the 'PRE' + 100000 to PRE + 100009
if I had increment the seed 9 times.
Now to get my where clause id value.
I have another query..that would return a list of id values.
I would want to put those id values in returned from that query in place of the id value.
So that query might be
select id from table a
So this would work like a curser....as this would require some level of iteration.
The number of records returned in the query should also equal the number of values I would have in the seed increment passed into by the stored proc.
I am fine with this being just known unless there is a way to not do the process if they result set and the seed count were different.
So lets say I have this update for one record.
Update table a
set tabla.Value = 'PRE100000'
where id = '12345'
I would want to pass a start seed value and an end seed value.
This would essentially create the 'PRE' + 100000 to PRE + 100009
if I had increment the seed 9 times.
Now to get my where clause id value.
I have another query..that would return a list of id values.
I would want to put those id values in returned from that query in place of the id value.
So that query might be
select id from table a
So this would work like a curser....as this would require some level of iteration.
The number of records returned in the query should also equal the number of values I would have in the seed increment passed into by the stored proc.
I am fine with this being just known unless there is a way to not do the process if they result set and the seed count were different.
Ryan Chong
is the table to be updated (Update SQL statement) is the same as the result table (Select SQL statement)?
This would essentially create the 'PRE' + 100000 to PRE + 100009
if I had increment the seed 9 times.
Now to get my where clause id value.
I have another query..that would return a list of id values.
I would want to put those id values in returned from that query in place of the id value.
From the above example we may assume that you want to update 9 records and that the another query will return 9 ids so each of these 9 records will have a value updated?
ASKER
yes the query result would be the where clause value in the update statement.
The seed increment logic would begin based on params passed in.
I was just making a note that the params seed amount should equal the number of values returned in the query results of the where clause...
Perhaps that could be good for error trapping.
If they were not the same the user putting in the params has an input error .
The seed increment logic would begin based on params passed in.
I was just making a note that the params seed amount should equal the number of values returned in the query results of the where clause...
Perhaps that could be good for error trapping.
If they were not the same the user putting in the params has an input error .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both Excelent solutions.
Thanks Vitor and Scott!!
Thanks Vitor and Scott!!