Okay so I wrote this stored procedure that is supposed to take some data from the PERSON_LS table, put it in a reportable format (which is why I used the max(case) statements), and then output it to another database for reporting from (database is called ReportPrep and the new table is called REUNION_MAILRULES_dim). Here is the sp I wrote:
It doesnt produce any errors but it does not populate the table in the reporting database . Any suggestions on what is wrong? Thanks!
asked Jul 23, 2012 at 09:54 PM in Default
What have you tried to resolve the problem so far?
I would suggest breaking the problem down in to component parts (Data selection, temporary object build, insert) and look to validate each.
First, have you attempted to run your initial selection statement/criteria to validate that it does in fact return data?
@muk I am clearly late for this train (too busy here at work), but the only problem that I see (which should not prevent the proc from still working properly) is in the case statements which lack the else part. Usually, it is a good idea to include it. For example, instead of
MAX ( CASE WHEN PERSON_LS.POS = 2 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END ) AS [Reunion Class 2],
I would use
MAX ( CASE WHEN PERSON_LS.POS = 2 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS ELSE NULL END ) AS [Reunion Class 2],
Again, this should not prevent your procedure from working properly. If you comment out 3 lines after your comment line reading
then your insert will become a select of the data from the table variable. Now you can execute the proc:
and you should see the records in results. If you do then there is nothing wrong with the proc, you just need to make sure that something calls it to actually execute.
answered Jul 31, 2012 at 07:52 PM