Complex stored procedure issue

[trompetaloco]

I have a need to validate certain input parameters on a stored procedure. The situation is this:

The stored procedure performs an order insert. As such, it contains a number of input parameters. Some of these require data validation prior to inserting the order. The validation info is maintained in a table. Here is a simple example of the what I am talking about:

create proc order_insert @p1 char(10), @p2 char(10), @p3 char(10)

as

insert order
values (@p1, @p2, @p3)

go

The validation table would contain this type of data:

ID Parm Value
1 @p2 x
2 @p3 y

I need to read the validation table to get the parameters to validate, then validate the data from the input parameters specified. I would just build the checks into the stored proc, but the validation info needs to be dynamic (ie. they might decide to validate @p1 in the future).

The root of the problem is how to take the value of the "Parm" column (ie. @p2, @p3) and use it to validate the data contained in that parameter(ie. @p2 should contain 'x' and @p3 should contain 'y').

I know that this could be accomplished via a temp table, but I am trying to avoid it.

 

[jhall156]

Which version of SQL Server are you using? JHall

 

[trompetaloco]

SQL 2000

 

[fluteplr]

I hate to ask what is probably a stupid question. And maybe you have simplified the example for us. But if you know in your validation table what the parm should be, why are you passing it as a parm? Why don't you just retrieve the value from the validation table?

 

[jhall156]

I think I kinda see what you're going for. One thing I noticed though is that the validation table should probably contain a little more information or more options about the parameter. As it is now it could only contain intelligence for one kind of comparison (I'm assuming equality). While you can write the comparison code in the stored procedure aren't you then defeating the purpose of maintaining a table based validation system? I think it might be an interesting design to allow for more than one entry per parameter and to include information about the business logic such as what kind of comparison operator to apply (=>< etc.), what kind of boolean operator i.e. AND, OR to apply between validation records, upper limits, lower limits etc. JHall

 

[trompetaloco]

Sorry. I did over simplify a bit. The validation table will actually contain the table and field name of the validation table for each parm to be validated (ie. the validation table will contain the name of the productcode parm, the table name of the Product table and the field name of the Product Code, if this is one of the parms to be validated). The routine will get the validation info from this table, then pull the list of all valid values (ie. a product list; Product.productcode), then get the data from the correct parm (ie. @productcode, this is the problem!!) and validate it against the list previously pulled.

I hope this clarifies the problem.

Thanks for the help.