Does not give details if a field is NULL

[Cpreston]

Hi

I have created a stored procedure that appeared to run ok. If it was true it send an email in good format with the right information. However, today it sent the email with the subject but no details.
I checked the view out and noticed the address1 field was null but information in the other fields. My hunch is because this files was NUll it did not bring the rest of the information through. Therefore in the scrip how of I allow for potential NUll values. Some of the script is enclosed where I set the variables and fetch statement it up. Any ideas please.

	DECLARE @message	varchar(max)
	DECLARE @Recipient1	varchar(max)
	DECLARE @Subject1	varchar(max)


	DECLARE @OrderNumber	varchar (max)
	DECLARE @JourneyNumber	varchar (max)
	DECLARE @OrderStatus	varchar (max)
	DECLARE @Name		varchar (max)
	DECLARE @Address1	varchar (max)
	DECLARE @City		varchar (max)
	DECLARE @County		varchar (max)
	DECLARE @PostCode	varchar (max)
	DECLARE @ProductCode	varchar (max)
	DECLARE @Description	varchar (max)

DECLARE MY_Cursor Cursor
	FOR 
	SELECT * FROM [servername].[dbo].[viewname] WITH (NOLOCK)


Open My_Cursor 
	
FETCH NEXT FROM MY_Cursor INTO @OrderNumber, @JourneyNumber, 
@OrderStatus, @Name, @Address1, @City, @County, @PostCode, 
@ProductCode, @Description 
	WHILE (@@FETCH_STATUS = 0)
		BEGIN
		SET @message = 'Journey with Waiting for Stock Status ' 
+ CHAR(13) + CHAR(10) +
			'---------------------------------' + CHAR(13) 
+ CHAR(10) +
			'OrderNumber: ' +  @OrderNumber + CHAR(13) + 
CHAR(10) +
			'JourneyNumber: ' + @JourneyNumber +  CHAR(13) 
+ CHAR(10) +
			'OrderStatus: ' + @OrderStatus +  CHAR(13) + 
CHAR(10) +
			'Name: ' + @Name +  CHAR(13) + CHAR(10) +
			'Address1: ' + @Address1 +  CHAR(13) + CHAR(10) 
+
			'City: ' + @City +  CHAR(13) + CHAR(10) +
			'County: ' + @County +  CHAR(13) + CHAR(10) +
			'PostCode: ' + @PostCode +  CHAR(13) + CHAR(10) 
+
			'ProductCode: ' + @ProductCode +  CHAR(13) + 
CHAR(10) +
			'Description: ' + @Description +  CHAR(13) + 
CHAR(10)

 

[bborissov]

Check ISNULL() and COALESCE() functions in BOL

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

ISNUll did the trick I think, code given in case someone needs an example. Thanks

'OrderNumber: ' +  isnull(@OrderNumber,'') + CHAR(13) + CHAR(10) +
			'JourneyNumber: ' + isnull( @JourneyNumber,'') +  CHAR(13) + CHAR(10) +
			'OrderStatus: ' + isnull(@OrderStatus,'') +  CHAR(13) + CHAR(10) +
			'Name: ' + isnull(@Name ,'') + CHAR(13) + CHAR(10) +
			'Address1: ' + isnull(@Address1,'') +  CHAR(13) + CHAR(10) +
			'City: ' + isnull(@City,'') +  CHAR(13) + CHAR(10) +
			'County: ' + isnull(@County,'') +  CHAR(13) + CHAR(10) +
			'PostCode: ' + isnull(@PostCode,'') +  CHAR(13) + CHAR(10) +
			'ProductCode: ' + isnull(@ProductCode,'') +  CHAR(13) + CHAR(10) +
			'Description: ' + isnull(@Description,'') +  CHAR(13) + CHAR(10)

 

[DaveInIowa]

Glad you were able to solve the problem.
This isn't related to the original post but may I make a suggestion? In my opinion it would make the code more readable. Declare a 2-char variable to replace all of your CHAR(13) + CHAR(10) references...

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
...
SET @message = '...' + @CrLf +
               'OrderNumber: ' +  ISNULL(@OrderNumber, '') + @CrLf +
               'JourneyNumber: ' + ISNULL( @JourneyNumber, '') +  @CrLf + ...

 

[bborissov]

Also the NOLOCK hint is not never good.
That way you will get a "dirty" records.
What will happen if you send eMail with "dirty" information and after that the user just rollback the transaction?

Borislav Borissov
VFP9 SP2, SQL Server