?
Solved

@datatype

Posted on 2003-03-26
17
Medium Priority
?
3,276 Views
Last Modified: 2010-05-18
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

0
Comment
Question by:__Holly__
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 2
  • 2
  • +1
17 Comments
 
LVL 1

Author Comment

by:__Holly__
ID: 8212481
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


0
 
LVL 7

Expert Comment

by:TroyK
ID: 8214109
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
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8214221
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.





0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 1

Author Comment

by:__Holly__
ID: 8214294
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.





0
 
LVL 1

Accepted Solution

by:
marcgale earned 780 total points
ID: 8214316
sp_executesql may help (note: must be nvarchar, not varchar).

--build the statement
declare @stmt nvarchar(2000)
set @stmt = N'Select @B = convert('+cast(@datatype as nvarchar) + N', @value)'
--now run it
exec sp_executesql @stmt

hth
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8214735
marc i like that im looking into it thanks
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8219645
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


0
 
LVL 1

Author Comment

by:__Holly__
ID: 8219648
cant i just wrap one SP with a second SP that will always return either 0 or 1?
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8219956
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'

0
 
LVL 1

Author Comment

by:__Holly__
ID: 8221151
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
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8227832
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.
------------------------------------------
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8227834
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



0
 
LVL 7

Expert Comment

by:TroyK
ID: 8228708
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
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8232521
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
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8239826
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??


0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8244835
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
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8247517
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
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question