quote:

---

The Guy is telling me that he is/was using objects called datagrid or form something...and to use those he created a mess of a sproc with cursors, permanent "temp" tables to denormalize the data so he could use these objects

---

CREATE procedure MEPPILFormGen  
	  @bsrecid 	int
	, @detached 	int
	, @Leaseid 	char(8)
	, @BG 		char(8)
	, @SUBBG 	char(15)
	, @Expdt 	char(10)
        , @APPUSER char(30), @rc int OUTPUT, @error int OUTPUT
, @rowcount int OUTPUT
AS

DECLARE   @location 	varchar(20)
	, @addr1 	varchar(60)
	, @city     	varchar(25)
	, @state 	varchar(2)
	, @pilrec 	int
	, @function 	varchar(3)

SET NOCOUNT ON
BEGIN TRAN
DECLARE @s datetime, @e datetime, @Error_Message varchar(255)
, @Error_Type int, @Error_Loc int, @sprc int 
SELECT @rc = 0, @s = GetDate()

IF EXISTS(SELECT * FROM PILTEMP2 WHERE RECID = @bsrecid)
  BEGIN
	DELETE FROM PILTEMP2 WHERE RECID = @bsrecid

	SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
	
	IF @error <> 0 BEGIN 
	   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1 
	   GOTO MEPPILFormGen_Err END
	
	IF @rowcount = 0 BEGIN 
	   SELECT @Error_Loc = 2, @Error_Type = 50002, @rc = 1
	   	   , @Error_Message = 
'Staging Table Not Cleared out for ID: ' 
+ CONVERT(varchar(10),@bsrecid)
	   GOTO MEPPILFormGen_Err END
  END



-- Get location name 

    SELECT @location = CASE WHEN @detached = 0 THEN BL.NAME
	  		 		       
ELSE 'Detached - ' + SUBSTRING(L.NAME,4,5)
		      END
	 , @addr1=BL.ADDR1
	 , @city=BL.CITY
	 , @state=BL.ST 
      FROM SLIM32PROD47..BUILDING BL
INNER JOIN SLIM32PROD47..LEASED L
	ON BL.PROPID=L.PROPID 
       AND BL.BLDGID=L.BLDGID
INNER JOIN SLIM32PROD47..BLDGSPAC B
        ON L.BLDGID=B.BLDGID 
       AND L.ID=B.ID
     WHERE  B.RECID = @bsrecid

IF @error <> 0 BEGIN 
   SELECT @Error_Loc = 3, @Error_Type = 50001, @rc = -1 
   GOTO MEPPILFormGen_Err END

IF @rowcount = 0 BEGIN 
   SELECT @Error_Loc = 4, @Error_Type = 50002, @rc = 1
   	   , @Error_Message = 'No Rows SELECTED For ID: ' 
+  CONVERT(varchar(10),@bsrecid)
   GOTO MEPPILFormGen_Err END

IF @rowcount > 1 BEGIN 
   SELECT @Error_Loc = 5, @Error_Type = 50002, @rc = 3
   	   , @Error_Message = 'More than 1 Row SELECTED For ID: ' 
+ CONVERT(varchar(10),@bsrecid)
   GOTO MEPPILFormGen_Err END

-- Check for function

SELECT @pilrec = PILRECID FROM PIL WHERE BSRECID = @bsrecid
IF @pilrec IS NOT NULL
BEGIN
	SET @function = 'UPD'
END
ELSE 
BEGIN
	SET @function = 'NEW'
END


IF @function = 'NEW'
BEGIN

EXEC MEPTEXTCONVERT 'PIL', @bsrecid


UPDATE PILTEMP2 
   SET STAFF1 = 0,STAFF2 = 0,STAFF3 = 0,STAFF4 = 0,STAFF5 = 0
,STAFF6 = 0,STAFF7 = 0,STAFF8 = 0,STAFF9 = 0
   WHERE RECID = @bsrecid

IF @detached = '0'
    BEGIN
	UPDATE PILTEMP2 
		  SET PANEL1VISIBLE = 'True', 
		          PANEL2VISIBLE = 'False',
		          PARAGRAPH0 = REPLACE(PARAGRAPH0,'XXX',@Expdt)	 
	WHERE RECID = @bsrecid
    END	
ELSE
    BEGIN
	UPDATE PILTEMP2 
		  SET PANEL1VISIBLE = 'False', 
		          PANEL2VISIBLE = 'True',
          		          PARAGRAPH0 = REPLACE(PARAGRAPH0,'XXX',@Expdt) 
	WHERE RECID = @bsrecid
    END	


SELECT @location as Location
, @state as State
, @city as City
, @addr1 as Addr1
, @Leaseid as LeaseId
, @BG as BG
, @SUBBG as SUBBG
, @Expdt as Expdt, 
               '' as CREComments, 
-- Change * to be all the columns in the PILTEMP2 table

	  * 
FROM PILTEMP2  WHERE RECID = @bsrecid



END   /* END NEW */
ELSE  /* UPDATE */
BEGIN

	EXEC MEPTEXTCONVERT1 'PIL', @pilrec, @bsrecid
	EXEC MEPSTAFFCONVERT1 'PIL', @pilrec, @bsrecid


	IF @detached = '0'
	    BEGIN
		UPDATE PILTEMP2 
		  SET PANEL1VISIBLE = 'True', 
		          PANEL2VISIBLE = 'False',
		          PARAGRAPH0 = REPLACE(PARAGRAPH0,'XXX',@Expdt)	 
		WHERE RECID = @bsrecid
        END	
	ELSE
   	BEGIN
		UPDATE PILTEMP2 
		  SET PANEL1VISIBLE = 'False', 
		          PANEL2VISIBLE = 'True',
                      PARAGRAPH0 = REPLACE(PARAGRAPH0,'XXX',@Expdt) 
		WHERE RECID = @bsrecid
	END	
	
	SELECT @location as Location
, @state as State
, @city as City
, @addr1 as Addr1
, @Leaseid as LeaseId
, @BG as BG
, @SUBBG as SUBBG
, @Expdt as Expdt, 	  
P.CREComments, 
-- Change * to be all the columns in the PILTEMP2 table
		  * 
	FROM PILTEMP2  P2
	JOIN   PIL P
 	ON P2.RECID = P.BSRECID
	WHERE RECID = @bsrecid

END /* END UPDATE */


COMMIT TRAN
MEPPILFormGen_Exit:
SET @e = GetDate()
EXEC [usp_LogProcCalls]  
  'MEPPILFormGen' , @s, @e, @APPUSER, @rowcount, @error, ' ', @sprc OUTPUT
SET NOCOUNT OFF
Return
MEPPILFormGen_Err:
ROLLBACK TRAN
RAISERROR  @Error_Type @Error_Message
GOTO MEPPILFormGen_Exit

GO