November 15, 2007 at 12:25 pm
For ETL, I am looking for a way to dynamically validate that input data is in the correct data format. I have a common procedure that has, passed to it, the input value (as varchar) along with the output format, length, and precision. For example, the parms might be: '123.334', Decimal, 14, 2
I want the program to indicate if there are are signifigant (non-zero) digits on either end of the number that would be lost or cause an error if it was converted to the Decimal(14,2) format.
November 15, 2007 at 3:09 pm
Well here is a start. Unfortunatly I could only get it to work as a procedure not a function (where it would be much more useful) but maybe with a bit more time you can get it there.
exec dbo.MyTest 'A','decimal',14,3
exec dbo.MyTest '123.34','decimal',14,3
CREATE PROCEDURE MyTest
(@Var nvarchar(100), @Format nvarchar(50), @Length Int, @Precision Int )
AS
BEGIN
DECLARE @Sql nvarchar (4000)
DECLARE @ReturnVal int
SET @ReturnVal = 1
IF @Length IS NOT NULL
SET @Format = @Format + '('+ CAST(@Length AS VarChar) + ISNULL(','+CAST(@Precision AS VarChar),'') + ')'
SET @Sql = 'BEGIN TRY ' + Char(13) +
' SELECT CAST('''+ @Var + ''' AS ' + @Format + ') ' + Char(13) +
'END TRY' + Char(13) +
'BEGIN CATCH ' + Char(13) +
' SET @ReturnVal = 0 ' + Char(13) +
'END CATCH '
EXEC sp_executeSQL @Sql, N'@ReturnVal Int OUTPUT', @ReturnVal OUTPUT
SELECT @ReturnVal
END
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply