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

avatar image

muk
440 33 35 40

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

avatar image

John Sansom
897 2 4

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

avatar image

Oleg
17.2k 3 7 28

(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.

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:

x1069
x457
x428

asked: Jul 23, 2012 at 09:54 PM

Seen: 1320 times

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

Copyright 2016 Redgate Software. Privacy Policy