Link to home
Start Free TrialLog in
Avatar of __Holly__
__Holly__

asked on

@datatype

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.

SPNAME: spValidateValue
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"
(
          @DataType varchar,
          @Value sql_variant,
          @answer bit OUTPUT
)
As

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)
BEGIN
     Select @answer = 1
END
ELSE
BEGIN
     Select @answer = 0
END


     /* set nocount on */
     return

Avatar of __Holly__
__Holly__

ASKER

Ok i rewrote it to check for each reasonable datatype.

but now it always returns false.

(i took out the Output parameter, so that it would return a normal count-- this seems a lot easier to implement (using a ADODB.Recordset instead of an ADODB.Command)

plz help me to fix this
___________________________________________________________

Alter Procedure spValidateValue
(
          @DataType varchar,
          @Value sql_variant
)
As



declare @answer int
declare @A varchar
declare @B sql_variant
declare @C varchar

Select @answer = 0
Select @A = convert(varchar, @value)

IF @DATATYPE = 'bigint'
BEGIN
   PRINT '@DATATYPE FOUND: bigint'
Select @B = convert(bigint, @value)
END
IF @DATATYPE = 'binary'
BEGIN
   PRINT '@DATATYPE FOUND: binary'
Select @B = convert(binary, @value)
END
IF @DATATYPE = 'bit'
BEGIN
   PRINT '@DATATYPE FOUND: bit'
Select @B = convert(bit, @value)
END
IF @DATATYPE = 'char'
BEGIN
   PRINT '@DATATYPE FOUND: char'
Select @B = convert(char, @value)
END
IF @DATATYPE = 'datetime'
BEGIN
   PRINT '@DATATYPE FOUND: datetime'
Select @B = convert(datetime, @value)
END
IF @DATATYPE = 'decimal'
BEGIN
   PRINT '@DATATYPE FOUND: decimal'
Select @B = convert(decimal, @value)
END
IF @DATATYPE = 'float'
BEGIN
   PRINT '@DATATYPE FOUND: float'
Select @B = convert(float, @value)
END
IF @DATATYPE = 'int'
BEGIN
   PRINT '@DATATYPE FOUND: int'
Select @B = convert(int, @value)
END
IF @DATATYPE = 'money'
BEGIN
   PRINT '@DATATYPE FOUND: money'
Select @B = convert(money, @value)
END
IF @DATATYPE = 'nchar'
BEGIN
   PRINT '@DATATYPE FOUND: nchar'
Select @B = convert(nchar, @value)
END
IF @DATATYPE = 'numeric'
BEGIN
   PRINT '@DATATYPE FOUND: numeric'
Select @B = convert(numeric, @value)
END
IF @DATATYPE = 'nvarchar'
BEGIN
   PRINT '@DATATYPE FOUND: nvarchar'
Select @B = convert(nvarchar, @value)
END
IF @DATATYPE = 'real'
BEGIN
   PRINT '@DATATYPE FOUND: real'
Select @B = convert(real, @value)
END
IF @DATATYPE = 'smalldatetime'
BEGIN
   PRINT '@DATATYPE FOUND: smalldatetime'
Select @B = convert(smalldatetime, @value)
END
IF @DATATYPE = 'smallint'
BEGIN
   PRINT '@DATATYPE FOUND: smallint'
Select @B = convert(smallint, @value)
END
IF @DATATYPE = 'smallmoney'
BEGIN
   PRINT '@DATATYPE FOUND: smallmoney'
Select @B = convert(smallmoney, @value)
END
IF @DATATYPE = 'sysname'
BEGIN
   PRINT '@DATATYPE FOUND: sysname'
Select @B = convert(sysname, @value)
END
IF @DATATYPE = 'tinyint'
BEGIN
   PRINT '@DATATYPE FOUND: tinyint'
Select @B = convert(tinyint, @value)
END
IF @DATATYPE = 'uniqueidentifier'
BEGIN
   PRINT '@DATATYPE FOUND: uniqueidentifier'
Select @B = convert(uniqueidentifier, @value)
END
IF @DATATYPE = 'varbinary'
BEGIN
   PRINT '@DATATYPE FOUND: varbinary'
Select @B = convert(varbinary, @value)
END
IF @DATATYPE = 'varchar'
BEGIN
   PRINT '@DATATYPE FOUND: varchar'
Select @B = convert(varchar, @value)
END


Select @C = convert(varchar, @B)

PRINT 'A: ' + CONVERT(varchar, @A)
PRINT 'B: ' + CONVERT(varchar, @B)
PRINT 'C: ' + CONVERT(varchar, @C)

IF (@A = @C)
BEGIN
     Select @answer = 1
END


IF @@ERROR <> 0
BEGIN
     Select @answer = 0
END

Select COUNT(*) AS ANSWER Where @Answer = 1


     return


Avatar of TroyK
TroyK
Holly;

Wouldn't it be easier to try and insert the row from your import and check for errors at that point?

You should be able to build up some pretty robust error handling using SQL Server's DTS services.

One thing to consider with the approach you're taking is this; you aren't accounting for the lengths of the "character" data types (char, varchar, nchar, nvarchar). By referencing the datatype name without its length portion, you are defaulting to a length of 1.

Similar comments apply to the precision and scale portions of decimal and numeric types.

You should look up these topics in Books Online under the relevant datatypes.

One final note:

To return a value from a stored procedure include the [integer_expression] in the RETURN statement, e.g. "RETURN 99 --Sets the return value for the stored proc to 99"

HTH,
TroyK, MCSD
Avatar of __Holly__
__Holly__

ASKER

i cant either convert variant/text data to a vb datatype and then translate it back to a SQL Datatype--- which sux when you account for User Defined Types.

or i can ask sql 'does this data fit into this datatype' and thats all im trying to do.





Avatar of __Holly__
__Holly__

ASKER

i cant either convert variant/text data to a vb datatype and then translate it back to a SQL Datatype--- which sux when you account for User Defined Types.

or i can ask sql 'does this data fit into this datatype' and thats all im trying to do.





ASKER CERTIFIED SOLUTION
Avatar of marcgale
marcgale
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of __Holly__
__Holly__

ASKER

marc i like that im looking into it thanks
Avatar of __Holly__
__Holly__

ASKER

ok.. i didnt have a chance to look at marks, because i am about 90% of the way there.

i just basically cause an error when i convert to a datatype that doesnt work-- and i need to SP to always either return 0 or 1-- not error.

in my current Sp, i did some special handlnig of the IsDate Function-- id like to avoid needing to do this for each datatype-- but that was the way that i fixed the date problem (when i passed a value that isnt a date, and tried to convert it to a date)

my biggest problem is what happens when i try to pass a # to a field that is supposed to be a date-- i definitely need to return a false on this, instead of erroring out.

heres my current Sp-- ill eval moving to Marcs' reccomendation if and when i can fix this @SQLERROR:
------------------------------------------------
@DATATYPE FOUND: int
Server: Msg 245, Level 16, State 1, Procedure spValidateValue, Line 76
Syntax error converting the varchar value 'ACS' to a column of data type int.

------------------------------------------------

Alter Procedure "spValidateValue"
(
            @DataType nvarchar(50),
            @Value sql_variant
)
As


declare @answer bit
declare @B sql_variant


Select @answer = 1


IF @DATATYPE = 'bigint'
BEGIN
      PRINT '@DATATYPE FOUND: bigint'
      Select @B = convert(bigint, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'binary'
BEGIN
      PRINT '@DATATYPE FOUND: binary'
      Select @B = convert(binary, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'bit'
BEGIN
      PRINT '@DATATYPE FOUND: bit'
      Select @B = convert(bit, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'char'
BEGIN
      PRINT '@DATATYPE FOUND: char'
      Select @B = convert(char, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'datetime'
      IF ISDATE(convert(varchar, @Value)) = 0
      BEGIN
            PRINT '@DATATYPE FOUND: datetime'
            select @answer = 0
            GOTO HAVEDATATYPE
      END
      ELSE
      BEGIN
            PRINT '@DATATYPE FOUND: datetime'
            Select @B = convert(datetime, @value)
            GOTO HAVEDATATYPE
END

IF @DATATYPE = 'decimal'
BEGIN
      PRINT '@DATATYPE FOUND: decimal'
      Select @B = convert(decimal, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'float'
BEGIN
      PRINT '@DATATYPE FOUND: float'
      Select @B = convert(float, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'int'
BEGIN
      PRINT '@DATATYPE FOUND: int'
      Select @B = convert(int, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'money'
BEGIN
      PRINT '@DATATYPE FOUND: money'
      Select @B = convert(money, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'nchar'
BEGIN
      PRINT '@DATATYPE FOUND: nchar'
      Select @B = convert(nchar, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'numeric'
BEGIN
      PRINT '@DATATYPE FOUND: numeric'
      Select @B = convert(numeric, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'nvarchar'
BEGIN
      PRINT '@DATATYPE FOUND: nvarchar'
      Select @B = convert(nvarchar, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'real'
BEGIN
      PRINT '@DATATYPE FOUND: real'
      Select @B = convert(real, @value)
      GOTO HAVEDATATYPE
END


IF @DATATYPE = 'smalldatetime'
      IF ISDATE(convert(varchar, @Value)) = 0
      BEGIN
            PRINT '@DATATYPE FOUND: smallint'
            select @answer = 0
            GOTO HAVEDATATYPE
      END
      ELSE
      BEGIN
            PRINT '@DATATYPE FOUND: smallint'
            Select @B = convert(smalldatetime, @value)
            GOTO HAVEDATATYPE
END

IF @DATATYPE = 'smallint'
BEGIN
      PRINT '@DATATYPE FOUND: smallint'
      Select @B = convert(smallint, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'smallmoney'
BEGIN
      PRINT '@DATATYPE FOUND: smallmoney'
      Select @B = convert(smallmoney, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'sysname'
BEGIN
      PRINT '@DATATYPE FOUND: sysname'
      Select @B = convert(sysname, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'tinyint'
BEGIN
      PRINT '@DATATYPE FOUND: tinyint'
      Select @B = convert(tinyint, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'uniqueidentifier'
BEGIN
      PRINT '@DATATYPE FOUND: uniqueidentifier'
      Select @B = convert(uniqueidentifier, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'varbinary'
BEGIN
      PRINT '@DATATYPE FOUND: varbinary'
      Select @B = convert(varbinary, @value)
      GOTO HAVEDATATYPE
END

IF @DATATYPE = 'varchar'
BEGIN
      PRINT '@DATATYPE FOUND: varchar'
      Select @B = convert(varchar, @value)
      GOTO HAVEDATATYPE
END





HAVEDATATYPE:
IF @@ERROR <> 0
BEGIN
      Select @answer = 0
END

Select @answer


Avatar of __Holly__
__Holly__

ASKER

cant i just wrap one SP with a second SP that will always return either 0 or 1?
Avatar of __Holly__
__Holly__

ASKER

THIS WORKS FINE
----------------------------------
EXEC spValidateValue 'INT', 123

just for the record, this is the call that makes it error out (and thus not return a parameter)

THIS IS BROKEN
----------------------------------
EXEC spValidateValue 'INT', 'ACS'

Avatar of __Holly__
__Holly__

ASKER

OK i tried marcs' method, and it is returning a whole bunch of false positives.

isnt there some sort of system stored proc that can help me here??


EXEC spValidateValue2 'DATETIME', '123.245'

RETURNS
1900-05-04 05:52:48.000

When it should return false. or 0 or whatever.


------------------------------------------
CREATE PROC spValidateValue2
(
@datatype varchar(50),
@value varchar(50)
)
AS

--build the statement
declare @stmt nvarchar(2000)

set @stmt = N'Select convert('+cast(@datatype as nvarchar) + N', ' + @value + ')'
--now run it
exec sp_executesql @stmt

IF @@ERROR <> 0
BEGIN
     Return 0
END
ELSE
BEGIN
     Return 1
END
Avatar of __Holly__
__Holly__

ASKER

Troy;

im sorry, is there a difference between the output parameter and the return value?


---------------------------------------------
I scrapped doing this as a SP and am currently using this VB code with limited preliminary success.

Basically, i log all of the request coming into the vb function and then i build a cross join of the datatypes and values in order to test this as much as possible.

what i need to do is to add a bunch of other datatypes and test the obscure datatypes in order to round out the function (in order to break it)

ill probably post my final code in a few more days.
------------------------------------------
Avatar of __Holly__
__Holly__

ASKER

Public Function ValidateValue(datatype As String, value As String) As Boolean
On Error Resume Next

Dim strLogSql As String
strLogSql = "EXEC CodeFunction.dbo.uspLogTextFieldValidation '" & datatype & "', '" & value & "'"

Docmd.Runsql strLogSql

Dim boolTmp As Boolean
Dim i As Integer

If datatype = "bigint" And IsNumeric(value) Then
    If CInt(value) >= CDec(value) Then
        GoTo success
    End If
End If

If datatype = "binary" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "bit" Then
    If value = "0" Or value = "1" Then
        GoTo success
    End If
End If

If datatype = "char" Then
    GoTo success
End If

If datatype = "DateTime" And IsDate(value) Then
    GoTo success
End If

If datatype = "decimal" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "float" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "int" And IsNumeric(value) Then
    If CInt(value) >= CDec(value) Then
        GoTo success
    End If
End If

If datatype = "money" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "nchar" Then
    GoTo success
End If

If datatype = "numeric" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "nvarchar" Then
    GoTo success
End If

If datatype = "real" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "smalldatetime" And IsDate(value) Then
    GoTo success
End If

If datatype = "smallint" And IsNumeric(value) Then
    If CInt(value) >= CDec(value) Then
        GoTo success
    End If
End If

If datatype = "smallmoney" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "sysname" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "tinyint" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "uniqueidentifier" And IsNumeric(value) Then
    GoTo success
End If

If datatype = "varbinary" Then
    GoTo success
End If

If datatype = "varchar" Then
    GoTo success
End If

cleanexit:
    ValidateValue = boolTmp
    Exit Function
success:
    boolTmp = True
    GoTo cleanexit
errhandler:
    Select Case Err.Number
    Case 13
        boolTmp = False
        Resume cleanexit
    Case Else
        boolTmp = False
        Debug.Print Err.Number & " - " & Err.Description
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly
        Resume Next
        Resume cleanexit
    End Select
End Function

Public Sub TestVerifyFields()
On Error GoTo errhandler

Dim strDataType As String
Dim strValue As String

Dim rst As New ADODB.Recordset

rst.Open "Select * FROM vwDataTypeValueCrossjoin Order by Value", CurrentProject.Connection

Do Until rst.EOF

strDataType = rst!datatype
strValue = rst!value

If ValidateValue(strDataType, strValue) = False Then
    Debug.Print "COLUMN MISMATCH -  TYPE: " & strDataType & " - " & "VALUE: " & strValue & " - ", vbOKOnly
End If

rst.MoveNext
Loop


cleanexit:
    Exit Sub
errhandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly
    Resume Next
    Resume cleanexit
End Sub



Avatar of TroyK
TroyK
Holly;

> im sorry, is there a difference between the output parameter and the return value?


CREATE PROC ReturnsStuff
  @OutputParam varchar(200) OUTPUT
AS
SET NOCOUNT ON

SET @OutputParam = 'I can be other datatypes in addition to int'

RETURN 99 --Can only be an int
GO

DECLARE @RetVal int, @MyParam varchar(200)

EXEC @RetVal = ReturnsStuff @MyParam OUTPUT

SELECT @RetVal 'Return Value', @MyParam 'Output Parameter'

HTH,
TroyK, MCSD
Avatar of ispaleny
ispaleny
Flag of Czechia image
CREATE PROC dbo.spValidateValue2
(
         @DataType varchar(50),
         @Value sql_variant,
         @answer bit OUTPUT
)
AS BEGIN
 declare @Exec nvarchar(4000)
 declare @Res sql_variant
 set @Exec='select convert('+@DataType+',@Value)'
 exec sp_executesql @Exec,N'@Value sql_variant',@Value=@Value
 if @@error<>0 set @answer=0
 else          set @answer=1
END
GO

declare @out bit
exec dbo.spValidateValue2 'numeric(1)',15,@out OUTPUT
select @out
Avatar of __Holly__
__Holly__

ASKER

ok guys im stoked i think that im finally getting somewhere.. but now i run this:
-----------------------
declare @out bit
exec dbo.spValidateValue3 'numeric(1)','15',@out OUTPUT
select @out
-----------------------
and i get this
-----------------------
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(1 row(s) affected)
-----------------------

shouldnt i get a 0 or 1 back?

thats all i need to do is to ALWAYS get a 1 or 0 back.

im just gonna fire this through VB and if i get 1, then the data fits, if the data doesnt fit in the datatype, then i am going to cancel that whole file being imported.

i just dont like dealing with the Sp that doesnt always return true or false..

what am i missing??


Avatar of ispaleny
ispaleny
Flag of Czechia image
VB - forget it

Public Sub Tester()
 Dim cmd As ADODB.Command
 On Error GoTo Fail
 Set cmd = New ADODB.Command
 cmd.ActiveConnection = con  'Add your active connection
 cmd.CommandType = adCmdUnknown
 cmd.CommandTimeout = 10
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "dbo.spValidateValue2"
 cmd.Parameters.Refresh
 cmd.Parameters("@DataType").Value = "Numeric(1)"
 cmd.Parameters("@Value").Value = 15
 On Error Resume Next
 cmd.Execute
 On Error GoTo Fail
 MsgBox "Was it correct ?: " + IIf(cmd.Parameters("@answer").Value, "YES", "NO")
 Exit Sub
Fail:
 Err.Clear
 MsgBox "Error:" + Err.Description
End Sub
Avatar of __Holly__
__Holly__

ASKER

i gotta test that-- i wasnt always getting a yes or no back from the sp; and i dont see how thats gonna help-- but ill recreate the sp and test

THANKS