none
SQL SERVER 2008 R2: Scalar-Value Function returns different data types values

    Question

  • Hi All,

    Do any of you know of away in SQL SERVER 2008 R2 to create a Scalar-Value Function that returns different data types values?

    Kind regards,
    Adam

    Friday, March 02, 2012 1:32 PM

Answers

  • I guess you are refering to the SQL Server relational engine, this is the discussion forum for the multi-dimensional server Analysis Services.

    Nevertheless, you can create scalar functions using SQL_Variant as return type:

    create function dbo.fn_demo()
    returns sql_variant
    as
    begin
    DECLARE @res sql_variant
    if DAY(getdate())<10 SET @res = 1 ELSE SET @res= 'more than one'
    return @res 
    end
    GO

    If we would call this function:

    select dbo.fn_demo()

    We get back either the number 1, or the string "more than one", dependent on the day of month.

    Is this what you're looking for?


    Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net

    Friday, March 02, 2012 3:04 PM

All replies

  • I believe SSRS can return different data type by expression like this way:

    =IIF(Fields!Logic.Value = "int", CInt(Fields!MyValue.Value), CStr(Fields!MyValue.Value)

    But, I'm not really sure if it will work as you expected.

    Help it helps.


    Regards, Nighting Liu

    Friday, March 02, 2012 2:07 PM
  • I guess you are refering to the SQL Server relational engine, this is the discussion forum for the multi-dimensional server Analysis Services.

    Nevertheless, you can create scalar functions using SQL_Variant as return type:

    create function dbo.fn_demo()
    returns sql_variant
    as
    begin
    DECLARE @res sql_variant
    if DAY(getdate())<10 SET @res = 1 ELSE SET @res= 'more than one'
    return @res 
    end
    GO

    If we would call this function:

    select dbo.fn_demo()

    We get back either the number 1, or the string "more than one", dependent on the day of month.

    Is this what you're looking for?


    Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net

    Friday, March 02, 2012 3:04 PM