Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL SERVER 2008 R2: Scalar-Value Function returns different data types values

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

  • Friday, March 02, 2012 1:32 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Friday, March 02, 2012 2:07 PM
     
      Has Code
    Sign In to Vote
    0

    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 3:04 PM
     
     Answered
    Sign In to Vote
    0

    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