CREATE   PROCEDURE p_ValidateProduct
	@productId				int,
	@validData 				bit output,
	@validOrderItemId		bit output,
	@validDisplayName		bit output,
	@validGenre				bit output,
	@validEsrb				bit output,
	@validDownloadSize		bit output,
	@validPrice				bit output,
	@validRecommended		bit output,
	@validSnippet			bit output,
	@validShortDescription	bit output,
	@validDescription		bit output

as begin
	-- Author:		John Reed
	-- Description:	Verifies that all required product data exists
	--				for a specified product.
	-- Tools:		ProductAdmin
	-- Revisions:	2006-01-11 - Created.	
	set nocount on
	
	declare @recommendedCount int
	declare @orderItemId varchar(64)
	declare @displayName nvarchar(64)
	declare @description nvarchar(2048)
	declare @shortDescription nvarchar(256)
	declare @snippet nvarchar(32)
	declare @genreId int
	declare @esrbId int
	declare @downloadSize int
	declare @price decimal(19,2)

	select	
		@orderItemId = OrderItemId,
		@displayName = DisplayName,
		@description = [Description],
		@shortDescription = ShortDescription,
		@snippet = Snippet,
		@genreId = GenreId,
		@esrbId = EsrbId,
		@downloadSize = DownloadSize,
		@price = Price
	from	t_Product
	where	ProductId = @productId

	if (@orderItemId != null) and (@orderItemId != '')
	begin
		set @validOrderItemId = 1
	end

	if (@displayName != null) and (@displayName != '')
	begin
		set @validDisplayName = 1
	end

	if (@description != null) and (@description != '')
	begin
		set @validDescription = 1
	end

	if (@shortDescription != null) and (@shortDescription != '')
	begin
		set @validShortDescription = 1
	end

	if (@snippet != null) and (@snippet != '')
	begin
		set @validSnippet = 1
	end

	if (@genreId != null)
	begin
		set @validGenre = 1
	end

	if (@esrbId != null)
	begin
		set @validEsrb = 1
	end

	if (@downloadSize != null)
	begin
		set @validDownloadSize = 1
	end

	if (@price != null)
	begin
		set @validPrice = 1
	end

	if	(@validRecommended = 1 and
		@validOrderItemId = 1 and
		@validDisplayName = 1 and
		@validDescription = 1 and
		@validShortDescription = 1 and
		@validSnippet = 1 and
		@validGenre = 1 and
		@validEsrb = 1 and
		@validDownloadSize = 1 and
		@validPrice = 1)
	begin
		set @validData = 1
	end
end

select
	@validOrderItemId =
		case when OrderItemId is null or OrderItemId = ''
		then 0 else 1 end,
	@validDisplayName =
		case when DisplayName is null or DisplayName = ''
		then 0 else 1 end,
	@validDescription =
		case when [Description] is null or [Description] = ''
		then 0 else 1 end,
	... and so on ...
from
	t_Product
where
	ProductId = @productId

price != null

price is not null

set ansi_nulls on
print 'ansi_nulls on'
if null = null print 'null = null true' else print 'null = null false'
if not null <> null print 'not null <> null true' else print 'not null <> null false'

set ansi_nulls off
print 'ansi_nulls off'
if null = null print 'null = null true' else print 'null = null false'
if not null <> null print 'not null <> null true' else print 'not null <> null false'


Results:
ansi_nulls on
null = null false
not null <> null false
ansi_nulls off
null = null true
not null <> null true