you need to convert/cast it each time. I made a function to use:
CREATE FUNCTION QuoteNull
(
@InputStr varchar(8000) --value to force to string
)
RETURNS
varchar(8000)
AS
BEGIN
RETURN COALESCE(''''+@InputStr+'''','null')
END
it puts single quotes around the value or just the word null if it is null, but you can customize it as necessary.
here is a version that handles formatting dates automatically:
CREATE FUNCTION QuoteNull
(
@InputStr sql_variant --value to force to string
)
RETURNS
varchar(8000)
AS
BEGIN
DECLARE @String varchar(8000)
SET @String=COALESCE(''''+ CASE SQL_VARIANT_PROPERTY(@InputStr,'BaseType')
WHEN 'datetime' THEN CONVERT(varchar(23),@InputStr,121)
ELSE CONVERT(varchar(8000),@InputStr)
END
+'''','null')
RETURN @String
END