none
Passing SQL Constants to CLR procedure/function

    Question

  • Some SQL Server functions such as those that work on dates use constants as a parameter. For example the DATEADD function takes as its first parameter a constant which cane be 'day', 'week', 'year' etc.

    I would like to write a CLR procedure that mimics dateadd but working on integer data warehouse keys in the form yyyymmdd. I would like the function call to work in exactly the same way as the built in function for example:

    dbo.DateKeyAdd(day, 10, 20120301)

    where the first parameter value is resolved as the SQL constant for day.

    Is this possible?


    Andrew Wiles - www.it-workplace.com - MDX made simple

    Monday, March 19, 2012 8:53 AM

All replies

  • Hi Andrew,

    You can call the DATEADD function directly as below:

    DECLARE @DATE INT=20120101
    SELECT DATEADD(DAY,10,CONVERT (DATETIME,CONVERT(CHAR(8),@DATE)))


    To create a function or stored procedure in SQL Server, the first parameter should be quoted by Single quotation marks, as ‘DAY’, or SQL Server cannot distinguish it as a string parameter.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    Tuesday, March 20, 2012 7:03 AM
  • Stephanie

    Thanks for the reply I am aware that this can be done in pure SQL however to do the full job in SQL functions I would also need to convert the response back to an integer which makes the resulting expression very cumbersome.

    The function I am implementing would provide a cleaner, more concise interface taking an integer as a the datekey parameter and returning an integer with no need for conversions.

    It is not a big deal to take a string as the first parameter to the function but I was interested in whether the SQL constants are accessible.


    Andrew Wiles - www.it-workplace.com - MDX made simple

    Tuesday, March 20, 2012 9:19 AM