It would really help if you would post the error you encountered but...
Based on the code you provided you most likely encounter a problem that your query tries to fetch multiple rows into a single variable. This one
SET @priority = (SELECT dbo.Jobs.ProcessStatus FROM dbo.Jobs WHERE dbo.Jobs.ProcessStatus = @NotificationTypeID);
If that's true you have few choices:
- ensure that you select only one row by modifying the conditions
- create a loop using a cursor where you handle all the returned rows
- instead of cursor use a single statement to make modifications in the database (if you're modifying the data)
If the error is something different, please post the full description.