asked
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!
@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 :)
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 -- 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.
9 People are following this question.
