Hi everybody,

I wrote the following SP (thanks for Mike Levy for advice to create 3 different SPs instead of one complicated). Note defendant field formatting. My question is: what is the correct and best way: do formatting inside SQL Server or do it in ASP, therefore return Def1LName and Def1FName? Thanks a lot in advance:

IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'USP_GetDetails_B' 
    AND  type = 'P')
    DROP PROCEDURE USP_GetDetails_B
GO
 
CREATE PROCEDURE USP_GetDetails_B
           	(@tnCartID int = null,
		 @tnContentID int = null )
              
AS 
        
/* **********************************************************************************
CREATED BY:     Nadya Nosonovsky
CREATED ON:     03/09/2003
PURPOSE:        This procedure retrieves information from CreditInfo
                for bankruptcy Credit Record type
 
INPUTS: tnCartID, tnContentID        
 
OUTPUTS:        
    ( RETURN CODES ) 
                                0 - Successful
                                1 - CartID is NULL and ContentID is Null                                  
                                Actual error code 
                               
                    
                              
USAGE EXAMPLE:
   DECLARE @RetStat int
   EXECUTE @RetStat = USP_GetDetails_B(4)
 
print 'Cart ID =' + RTRIM(CONVERT(varchar(30), @tnCartID)) + ' Error code = ' + RTRIM(CONVERT(varchar(30), @RetStat))
 
UPDATES (date,name,details): 
    
********************************************************************************** */
 
SET NOCOUNT ON -- Do not want to show 1 row affected message
-- First do basic validation
 
 IF @tnCartID IS NULL and @tnContentID IS NULL
      BEGIN
        RAISERROR ('ERROR: At least one parameter should contain a value.',16,1)
        RETURN 1
      END
 
  DECLARE @Err int, @NumRecs int
  IF NOT @tnContentID IS NULL                                                   
     BEGIN
       SELECT CreditType as [Credit Type], 
                     State, County, Registry, City, 
        rtrim(Def1LName) + CASE Def1FName WHEN ''
                     THEN ' ' ELSE ', ' END + Def1FName AS Defendant,              Address, DocketRef as [Case#], InitDate as [Filing Date] 
                     FROM CreditInfo where CredID in 
			   (select CredID from CartDetail 
	      	   inner Join CartContent on 
		         CartDetail.CartContentID = CartContent.CartContentID 
		         where CartContent.CartContentID = @tnContentID)  ORDER BY 1, 2, 4, 5, 6, 7, 8
		         SELECT @NumRecs = @@ROWCOUNT, @err = @@ERROR
		         IF @err !=0 
		             RAISERROR ('ERROR: Problem with Bankruptcy records!' ,16,1)            
      END
   ELSE  -- ContentID is NULL
      BEGIN
      SELECT CreditType as [Credit Type], 
                     State, County, Registry, City, 
   rtrim(Def1LName) + CASE Def1FName WHEN ''
                     THEN ' ' ELSE ', ' END + Def1FName AS Defendant,                   Address, DocketRef as [Case#], InitDate as [Filing Date] 
                     FROM CreditInfo where CredID in 
			   (select CredID from CartDetail 
	      	   inner Join CartContent on 
		         CartDetail.CartContentID = CartContent.CartContentID 
		         where CartContent.CartID = @tnCartID and CartContent.CreditCategory = 'B')  ORDER BY 1, 2, 4, 5, 6, 7, 8
		         SELECT @NumRecs = @@ROWCOUNT, @err = @@ERROR
		         IF @err !=0 
		             RAISERROR ('ERROR: Problem with Bankruptcy records!' ,16,1) 
      END
IF @err != 0
  RETURN @Err
ELSE
   RETURN 0

GO
 
-- Now test procedure
DECLARE @tnCartID int, @RetStat int 
SET @tnCartID = 4
EXECUTE @RetStat = USP_GetDetails_B @tnCartID
 
print 'Cart ID =' + RTRIM(CONVERT(varchar(30), @tnCartID)) + ' Error code = ' + RTRIM(CONVERT(varchar(30), @RetStat))
GO