tsql : best way to cast variables to string type?

Question

I wrote a tsql procedure which inserts a string into a text file which means it requires all variables be converted to a string. Instead of using a case by case statement, is there an easier to do this which encompasses all cases and forces whatever type to a string type?

thanks in advance

Answer 1

All-caps is literal text, lower-case is something into which you should interpolate a value:

CAST(expression AS CHAR)
-- or:
CAST(expression AS VARCHAR)

You can optionally specify a length.

Unfortunately, it's a bit harder with datetimes, if you want to format it in any way that's different from the default representation.

My info came from the MSDN site. I think you'll have to read that site, and others, carefully and play around with it a bit, but hopefully the CAST function will be a good start.

Good luck!

Answer 2

I do not think there is--and if there were, I wouldn't trust it, as there'd probably be very little control over the overall formatting. Build your own, and it'll look exactly as it needs to look. Factors to contemplate:

• Date formatting
• Numbers, leading zeros, decimal places, +/- signs
• Line length, word wrap
• Leading spaces, trailing spaces, spaces between strings that don't have leading or trailing spaces

...it goes on and on.

Answer 3

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