Posted on 2003-03-26
Hey I am trying to make a SP that will take a SQL Server Datatype and a Value and compare the value to the datatype, and return whether or not the value will fit into that datatype.
PARAMETERS: @Datatype, @Value
RETURNS: @Answer (whether that value will fit into that datatype)
This is relatively straightforward, but the complication is that I have to compare both values as varchar in order to suppress converting Numbers into Dates and Dates into Numbers; etc.
this is going to be fired in an import routine that imports text files into SQL Server. Im basically firing this for each value (20 columns, hundreds of rows) that gets imported-- so it needs to be pretty bulletproof.
I am getting this error:
Server: Msg 170, Level 15, State 1, Procedure spValidateValue, Line 14
Line 14: Incorrect syntax near '@datatype'.
I would like to do this without having to list all the datatypes in this SP in a CASE WHEN THEN, etc--
(because i dont want to have to maintain this SP)
HELP! 425 378 5417
PS - im not sure im doing the return parameter correctly, can someone clue me in on that?
Create Procedure "spValidateValue"
@answer bit OUTPUT
declare @A varchar
declare @B sql_variant
declare @C varchar
Select @A = convert(varchar, @value)
Select @B = convert(@datatype, @value)
Select @C = convert(varchar, @B)
IF (@A = @C) AND LEN(@A) = LEN(@C)
Select @answer = 1
Select @answer = 0
/* set nocount on */