locked
using function in select query RRS feed

  • Question

  • hi,

    I want to use a scalar function in the select query.

    The function is for converting the date to current date.

    I want to select 2 of the columns for a table and the key i give as parameter for fetching details of that key ID.

    How can i use the function.

    regards,

    Dpkas

     

    Friday, April 15, 2011 12:24 PM

Answers

  • First..."The function is for converting the date to current date.". Sounds unusual. Why not to use GETDATE()? Why a scalar function to perform this?

    Secondly, the query goes as

    -- if parameter of your function is a column of the table
    Select col1,col2,dbo.YourScalarFunction(col3) as FunctionValue
    from YourTable
    where TableKey = @paramkey
    
    -- if parameter of your function is a parameter
    Select col1,col2,dbo.YourScalarFunction(@param) as FunctionValue
    from YourTable
    where TableKey = @paramkey
    

    Please visit my Blog for some easy and often used t-sql scripts
    • Proposed as answer by Naomi N Friday, April 15, 2011 1:55 PM
    • Marked as answer by dpkas Wednesday, April 20, 2011 10:43 AM
    Friday, April 15, 2011 12:38 PM

All replies

  • All you have to do is specify that function in your select statement. Something like

    SELECT ColA, ColB, dbo.Function_Name(date_col) AS Dt FROM TABLE_NAME


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Friday, April 15, 2011 12:29 PM
  • Just reference the function with the schema name in front of it -- such as dbo. for instance; however, I doubt that you need to use a scalar function something like this.

    For instance:

    select
      column1,
      column2,
      dbo.yourDateFunction('Some Argument')
    from yourTable
    where key_ID = @someValue

    Another alternative to the scalar function:

    select cast(current_Timestamp as date)
    /* -------- Output: --------
    ----------
    2011-04-15
    */
    
     

    Friday, April 15, 2011 12:30 PM
  • First..."The function is for converting the date to current date.". Sounds unusual. Why not to use GETDATE()? Why a scalar function to perform this?

    Secondly, the query goes as

    -- if parameter of your function is a column of the table
    Select col1,col2,dbo.YourScalarFunction(col3) as FunctionValue
    from YourTable
    where TableKey = @paramkey
    
    -- if parameter of your function is a parameter
    Select col1,col2,dbo.YourScalarFunction(@param) as FunctionValue
    from YourTable
    where TableKey = @paramkey
    

    Please visit my Blog for some easy and often used t-sql scripts
    • Proposed as answer by Naomi N Friday, April 15, 2011 1:55 PM
    • Marked as answer by dpkas Wednesday, April 20, 2011 10:43 AM
    Friday, April 15, 2011 12:38 PM