Stored Procedure vs User Defined Function for Error Handling
My ERP database uses non-nullable datetime fields. However, it enters '' for the datetime when one isn't available and returns ‘1900-01-01 00: 00: 00.000’ as the value.
I want to suppress the 1900 dates while stripping the Date only from the Datetime field. I created the following UDF to do that:
CREATE FUNCTION开发者_如何学运维 ExtractDate(@DirtyDate DATETIME)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @CleanDate VARCHAR(10)
SELECT @CleanDate =
CASE
WHEN @DirtyDate = '' THEN ''
ELSE CONVERT(VARCHAR(10), @DirtyDate, 101)
END
RETURN @CleanDate
END
This works, but I wanted to add error handling in case a user used it on something other than a datetime field. After some Googling, I found out that this isn't possible with UDF.
However, if I write this as a stored procedure, would I still be able to call it in a select statement? Can someone point me in the right direction?
No, you can't call a stored proc in a select statement.
I applaud your ambition in wanting to include error handling, but your best bet is to check for that on the app side - don't allow people to use the function on non-date fields.
精彩评论