stored procedure to format data from table and put in new table

Question

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:

USE [coll18_sox]
GO
/****** Object:  StoredProcedure [dbo].[SP_PERSON_LS_PREP]    Script Date: 07/23/2012 16:47:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_PERSON_LS_PREP]
AS
BEGIN
  DECLARE @ProcessedData AS TABLE (ID varchar(10), ReunionClass1 varchar(10),
  ReunionClass2 varchar(10), ReunionClass3 varchar(10), MailRules1 varchar(10),
  MailRules2 varchar(10), MailRules3 varchar(10))

  -- get processed data
  INSERT INTO @ProcessedData (ID, ReunionClass1,
  ReunionClass2, ReunionClass3, MailRules1,
  MailRules2, MailRules3)
  SELECT ID,MAX(CASE WHEN PERSON_LS.POS = 1 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END) AS [Reunion Class 1], 
                      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], 
                      MAX(CASE WHEN PERSON_LS.POS = 3 AND PERSON_LS.REUNION_CLASS IS NOT NULL THEN PERSON_LS.REUNION_CLASS END) AS [Reunion Class 3], 
                      MAX(CASE WHEN PERSON_LS.POS = 1 AND PERSON_LS.MAIL_RULES IS NOT NULL THEN PERSON_LS.MAIL_RULES END) AS [Mail Rules 1], 
                      MAX(CASE WHEN PERSON_LS.POS = 2 AND PERSON_LS.MAIL_RULES IS NOT NULL THEN PERSON_LS.MAIL_RULES END) AS [Mail Rules 2], 
                      MAX(CASE WHEN PERSON_LS.POS = 3 AND PERSON_LS.MAIL_RULES IS NOT NULL THEN PERSON_LS.MAIL_RULES END) AS [Mail Rules 3]
FROM   PERSON_LS GROUP BY ID

  -- check target and delete
  IF EXISTS (SELECT * FROM ReportPrep.dbo.REUNION_MAILRULES_dim)
  BEGIN
    DELETE FROM ReportPrep.dbo.REUNION_MAILRULES_dim
  END

  -- insert fresh
  INSERT INTO ReportPrep.dbo.REUNION_MAILRULES_dim (ID, ReunionClass1,
  ReunionClass2, ReunionClass3, MailRules1,
  MailRules2, MailRules3)
  SELECT ID, ReunionClass1,
  ReunionClass2, ReunionClass3, MailRules1,
  MailRules2, MailRules3 FROM @ProcessedData

END

It doesnt produce any errors but it does not populate the table in the reporting database . Any suggestions on what is wrong? Thanks!

Answer 1

0

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?

answered Jul 24 '12 at 06:12 AM

John Sansom
897 ● 2

Yes I did.

Jul 24 '12 at 02:44 PM muk

(comments are locked)

Answer 2

@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

-- insert fresh

then your insert will become a select of the data from the table variable. Now you can execute the proc:

exec [dbo].[SP_PERSON_LS_PREP];

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.

stored procedure to format data from table and put in new table

Follow this question

Related Questions