x

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

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!
more ▼

asked Jul 23, 2012 at 09:54 PM in Default

muk gravatar image

muk
400 31 33 37

Thanks @Oleg! That solved it! can you post as a response so I can mark it as correct?
Aug 01, 2012 at 08:43 PM muk
@muk Do you mean adding the else part to case statement? Please let me know because this would be quite a shocker for me. I mean I would never dare to have a case statement without the else part, but still, the lack of it should not break your procedure, so I am puzzled :)
Aug 01, 2012 at 09:11 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?
more ▼

answered Jul 24, 2012 at 06:12 AM

John Sansom gravatar image

John Sansom
897 2

Yes I did.
Jul 24, 2012 at 02:44 PM muk
(comments are locked)
10|1200 characters needed characters left

@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.
more ▼

answered Jul 31, 2012 at 07:52 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x412
x371

asked: Jul 23, 2012 at 09:54 PM

Seen: 1027 times

Last Updated: Aug 01, 2012 at 09:11 PM