SQL 2005 - Variant Parameter Question

Question

I am working on a function that will be used by no less than 10 SProc's, and will probably grow once it is ironed out.

Problem i am running into is that i do not want to develop a function for each Data Type, which is why the SQL_VARIANT data type is looking pretty convenient for the action. I know is can do the ISNULL check on the data type but i also want to check to see if the Value being passed is a valid Number but the ISNUMERIC function does not work with SQL_VARIANT and I'm not too sure about the SQL_VARIANT_PROPERTY function.

Code so far:

CREATE FUNCTION dbo.mpt_Format_Number
(
    @value SQL_VARIANT
    , @money BIT
)
RETURNS VARCHAR
AS
BEGIN
    --Check for NULL value
    IF ISNULL(@value) BEGIN
        -- Value IS NULL, return NULL
        RETURN NULL
    END ELSE BEGIN
        -- Value is NOT NULL
        DECLARE @TMP VARCHAR
    END 
END

To make it easier to read, i flipped the ISNULL check, so as to not confuse anyone ;) — GoldBishop, Sep 01 '11 at 15:18

t-clausen.dk · Accepted Answer · 2011-09-02T16:48:00.390

4

CREATE FUNCTION dbo.mpt_Format_Number 
( 
    @value SQL_VARIANT 
    , @money BIT 
) 
RETURNS VARCHAR 
AS 
BEGIN 
--Check for NULL value 
IF @value is null  
          -- Value IS NULL, return NULL 
    RETURN NULL 
ELSE 
BEGIN 
    -- Value is NOT NULL 
    if isnumeric(convert(varchar(max), @value)) = 1 RETURN 'Y' -- is valid number
    --DECLARE @TMP VARCHAR 

END  
return 'N' --is not valid number 
END

You can always test the property type with this syntax. Should be easy to incooperate in your function.

declare @t SQL_VARIANT
set @t = '3'
select SQL_VARIANT_PROPERTY(@t, 'basetype')

Result:

varchar

edited Sep 02 '11 at 16:48

answered Sep 01 '11 at 15:29

t-clausen.dk

43,517
12
59
92

cast worked fine for me, cast and convert can normally be used for the same, only difference is that you can add a format to the convert – t-clausen.dk Sep 01 '11 at 15:38
@tclausen - the SQL "compiler" kept puking on it and said i needed to use Convert instead of Cast – GoldBishop Sep 01 '11 at 16:47
@GoldBishop i see the problem, I am using 2008, where microsoft has fixed the bug. Sorry about that. I changed it. I hope everything works now – t-clausen.dk Sep 01 '11 at 17:46
@tclausen - Its all good and i actually built upon the design. I will post the resulting Function for yall to brow-beat ;) – GoldBishop Sep 01 '11 at 19:55

score 0 · Answer 2 · answered Sep 05 '11 at 14:41

As a final implementation here is the full function as it was used.

ALTER FUNCTION [dbo].[_mpt_Format_Number]
(
    @value SQL_VARIANT
    , @money BIT = 0
)
RETURNS VARCHAR(max)
AS
BEGIN
    DECLARE @ret VARCHAR(MAX)
    --Check for NULL value
    IF @value IS NULL BEGIN
        -- Value IS NULL, return NULL
        SET @ret = 'NULL'
    END ELSE BEGIN
        -- Value is NOT NULL

        --Check for Numeric Value
        IF ISNUMERIC(CONVERT(VARCHAR, @value)) = 0 BEGIN
            --Value is NOT a Number, return NULL
            SET @ret =  'NULL'
        END ELSE BEGIN
            --Value IS a Number
            declare @isNeg BIT
            declare @tmp varchar(max)
            set @tmp = convert(varchar(max), round(cast(@value as money), 0), 1)

            --Check if value is negative
            if @value < 0 begin
                --Value is Negative
                set @isNeg = 1
                --Remove the negative sign
                set @tmp = replace(@tmp, '-', '')
            end 

            --Remove the decimal plus any digits to the right of the decimal
            set @tmp = left(@tmp ,len(@tmp) - 3)

            --Is money set to True
            if @money = 1 begin
                --Pre-pend the dollar sign to value
                set @tmp = '$' + @tmp
            end

            --Is isNeg set to True
            if @isNeg = 1 begin
                --Encapsulate the value with parenthesis
                set @tmp = '(' + @tmp + ')'
            end

            SET @ret =  @tmp
        END 
    END 

    RETURN @ret
END

SQL 2005 - Variant Parameter Question

2 Answers2