In SQL Server 2008, how can I check if a varchar parameter can be converted to data type money?

Question

I have a stored procedure that I use to insert data from csv. The data itself is a combination of types, some tests, some dates, and some money fields. I have to guarantee that this data will be saved even if it is not formatted correctly, so I save them all in varchars. Later, as soon as the data is verified and verified, they will be moved to another table with the corresponding data types.

When I insert into the first table, I would like to make a check that sets the flag (bit column) in the row if it needs attention. For example, if in which money number there are letters, I must indicate this line and add the column name to the additional errormsg field that I have. Then I can use this flag to find and highlight for the users in the interface the fields that they should edit.

Date parameters seem easy, I can just use IF ISDATE(@mydate) = '0'to check if this parameter can be converted from varchar to datetime. But it looks like I can't find ISMONEY () or anything that is remotely equivalent.

Does anyone know what to call in order to check whether it is possible to finish converting the contents of the cook into money?

EDIT: I haven't tested it yet, but what do you think of such a feature ?:

CREATE FUNCTION CheckIsMoney 
(
   @chkCol varchar(512)
)
RETURNS bit
AS
BEGIN
 -- Declare the return variable here
 DECLARE @retVal bit

SET @chkCol = REPLACE(@chkCol, '$', '');
SET @chkCol = REPLACE(@chkCol, ',', '');

IF (ISNUMERIC(@chkCOl + 'e0') = '1')
    SET @retVal = '1'
ELSE
    SET @retVal = '0'

RETURN @retVal

END
GO

Update

Just finished testing the above code and it works!

Answer 1

money is decimal, so you check this way

Do not use ISNUMERIC out of the box, though: it is unreliable. Use this:

ISNUMERIC(MyCOl + 'e0')

Please note that if you have 6 decimal places, it will be lost when converting to money

Another question with additional information: How to determine the value of a field that cannot be converted to (decimal, float, int) in SQL Server

Edit:

You can do this on one line if you want

ISNUMERIC(REPLACE(REPLACE(@chkCOl, '$', ''), ',', '') + 'e0')