开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜