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

Answer 1

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

Answer 2

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