How to create a new column in SELECT CASE when string contains Arabic words?
Since you are using Unicode / UTF-16 (i.e. NVARCHAR), you do not need either the CAST or even an Arabic_* collation. The only requirement for string literals is to prefix them with a capital-&quot;N&quot;:
SELECT 'قدوم' COLLATE Arabic_CI_AS, '-o-' AS [-o-], 'رجوع' COLLATE Arabic_CI_AS;
-- ???? -o- ????

SELECT N'قدوم' COLLATE French_CI_AS, '-o-' AS [-o-], N'رجوع' COLLATE French_CI_AS;
-- قدوم -o- رجوع

(the -o- field is used as a separator to prevent the Bi-Direction formatting, inherent with Arabic and Hebrew code points, from displaying the second column on the left in the T-SQL comment below the query when rendered in HTML)
When using NVARCHAR (including NCHAR and the should-never-be-used NTEXT), the encoding is always UTF-16, hence the character set never changes (it's always Unicode, which is a single character set). In this case, the collation mainly affects sorting and comparisons (which are not being done here).
If you were using VARCHAR, then an Arabic collation would be needed in order to set the code page in order to support the Arabic characters (Windows Code Page 1256).
The reason why your initial 'قدوم' returned ???? is that the string literal was not prefixed with N, making it a VARCHAR string, in which case it was converted to the code page associated with the default collation of the current database you were using, which clearly was not an Arabic collation. This conversion happens as the query is initially parsed, which means that your string was already ???? before the cast operation converted it to NVARCHAR. If you had been using a database that had an Arabic default collation, then your string literal would have worked even without the N prefix, and the CAST to NVARCHAR would have been just as unnecessary.
 
For more information on working with strings / encodings / Unicode / collations, please visit my site: Collations Info

P.S. I would recommend that you not be doing old-style JOINs such as FROM TableA, TableB WHERE TableA.JoinColumn = TableB.JoinColumn. While that syntax has not been deprecated, the related outer join syntax — *= and =* — was actually removed in SQL Server 2005. So, for consistency (and personally, I would also throw in readability), you should instead use the INNER JOIN clause. Also, it's always best to prefix object names with their Schema name. The end result is:
FROM dbo.TableA
INNER JOIN dbo.TableB
        ON TableB.JoinColumn = TableA.JoinColumn