SQL Server 2005 query Decimal to String help

[JuggaloBrotha]

I'm still getting a crash course in SQL Server 2005 queries.

I have a query (a view actually):

SELECT     TOP (100) PERCENT dbo.Items.ItemID, dbo.Items.CatID, dbo.Items.Name, dbo.Items.URL, dbo.Items.Target, dbo.Items.SP, 
                      dbo.Items.FileSize + ' ' + dbo.SizeTypes.SizeType AS TotalFileSize
FROM         dbo.Items INNER JOIN
                      dbo.SizeTypes ON dbo.Items.SizeType = dbo.SizeTypes.SizeID
ORDER BY dbo.Items.CatID, dbo.Items.SortOrder

And what I need is the

dbo.Items.FileSize + ' ' + dbo.SizeTypes.SizeType AS TotalFileSize

part to show the FileSize (which is a decimal, precision of 3) to be shown as a string if it's not null and I can't seem to figure it out. In Access I would normally do this:

IIf([FileSize] Is Not Null, [FileSize] & " " & [SizeTypes].[SizeType] , "") As TotalFileSize

I just don't know SQL Server's equivalent.

 

[JuggaloBrotha]

Ok, I've got part of it figured out, the formatting. Here's the query now:

SELECT     TOP (100) PERCENT dbo.Items.ItemID, dbo.Items.CatID, dbo.Items.Name, dbo.Items.URL, dbo.Items.Target, dbo.Items.SP, CONVERT(VARCHAR(10), 
                      dbo.Items.FileSize) + ' ' + dbo.SizeTypes.SizeType AS TotalFileSize
FROM         dbo.Items INNER JOIN
                      dbo.SizeTypes ON dbo.Items.SizeType = dbo.SizeTypes.SizeID
ORDER BY dbo.Items.CatID, dbo.Items.SortOrder

Problem is if FileSize is null then it doesn't return that row, but I need it to be returned with the TotalFileSize field being null

 

[MattP]

Small example assuming 3 columns in table FileSize, SizeType, and FileName.

SELECT CASE WHEN FileSize IS NOT NULL 
	THEN CAST(FileSize AS VARCHAR) + ' ' + SizeType 
	ELSE '' 
	END As TotalFileSize
	,[FileName] 
FROM TestTable