SQL side validation

[KatherineF]

I need to check data types of values that have been entered from UI. I am going to create a stored proc that will take two parameters (@FieldName, @Value)

I have a table where I can get data types for @FieldName from.
SELECT @FieldType = FieldType
FROM TableName
WHERE FieldName = @FieldName

So by now I have @FieldName, @FieldType and @Value. @FieldType can be varchar, char, bit, int, long, datetime, etc.

I can't figure out the logic I have to use in order to compare data type of @Value and @FieldType.

The stored proc has to return true or false.
There are only two useful functions that I can use ISNUMERIC and ISDATE, but what about all other data types?

 

[Catadmin]

I'm a little confused at your description, but I'm assuming that you are trying to literally find out if @value's datatype is the same datatype as @FieldName's datatype. Are the input parameters literally column names from a specific table?

Or are you trying to compare values on these variables?

Can you give us a sample of how you expect to input, what the data from the table(s) looks like and how you expect the results to be outputted?





Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[KatherineF]

--- Are the input parameters literally column names from a specific table?


Yes, they are. @FieldName and @Value are columns (varchar) in table called "Table1".

The page I am working with displays one label and one textbox (label - @FieldName from "Table1", textbox value - @Value from "Table1")

And there is another table (let's call it "Table2") where I can get datatypes for @FieldName from.

I need to make sure that @Value being inserted from the textbox on that page is the same datatype as FieldType from "Table2" for corresponding FieldName.


Table1

FieldID (not PK) | FieldName (varchar) | Value (varchar)
-------------------------------------
1 | BookID | 12
2 | ContractType | Annual
3 | StartDate | 12/12/2004


Table2

FieldID (PK) | FieldName | FieldType
-------------------------------------
1 | BookID | int
2 | ContractType | varchar
3 | StartDate | datetime



Thank you for helping me out Sorry for my explanation.

 

[KatherineF]

do you have any ideas how to implement this validation?
Thanks.

 

[Catadmin]

Give me a little time to play with this. I'll let you know if I can figure it out or not this morning.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Catadmin]

I've been thinking about this, Katherine, I don't know of a good way to compare datatypes. Compare values, yes. Compare DTs, not really.

HOWEVER, you could just do a Convert on the value being inserted to make sure that it will be the same datatype in both tables. This would keep you from having to do the datatype comparison in the first place.

Does anyone else have any thoughts? I've scratched my brain all morning and Convert/Cast is all I can come up with.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!