Validation in stored procedure

Validation in stored procedure

Post by Larry Johnso » Sat, 11 Nov 2000 04:00:00



I'm working (happily) through Wrox Beginning VB SQL Server. One of the
examples does extensive data validation (valid length, etc.) in a
stored procedure. In VB and Access projects I'm used to validating
data on the contol's BeforeUpdate event so that only valid data would
be passed to the stored procedure. It seems that this type of
validation would provide the user with more information.

Is validation in stored procedures the typical approach with SQL
Server?

 
 
 

Validation in stored procedure

Post by Dick Christop » Sun, 12 Nov 2000 04:00:00


Hi Larry,

Quote:> Is validation in stored procedures the typical approach with SQL
> Server?

I am unfamilar with the book of which you speak, so I don't know the context
of these validations, I would agree, if you can validate data in the user
interface that is better,

But (for example) we have a database which collects POS data from a fairly
unreliable system in the UK, Our system has a fairly large set of validation
routines, to validate this data prior to inserting it in our database. In
this case there is no active user (a user sitting at a PC)  to report these
errors to so they are logged and then are very capable staff, figures out
what the bad data should be.

Additionally (In general) at the user interface level, there may be
validations which would require a check against other data in the database,
such as "Is the Customer number you just entered, one of our 1,000,000 valid
customers?" since you probably don't want to pull all 1,000,000 customers
into the user interface, you would have to make a trip out to the DB to
check, and a stored procedure is a good way to do this.

HTH

-Dick Christoph


Quote:> I'm working (happily) through Wrox Beginning VB SQL Server. One of the
> examples does extensive data validation (valid length, etc.) in a
> stored procedure. In VB and Access projects I'm used to validating
> data on the contol's BeforeUpdate event so that only valid data would
> be passed to the stored procedure. It seems that this type of
> validation would provide the user with more information.

> Is validation in stored procedures the typical approach with SQL
> Server?


 
 
 

1. Foreign keys validation in a stored procedure

I wrote some stored procedures that insert data in the db.
Some tables contain foreign keys

from other tables, e.g.

CREATE TABLE Table1 (
       Table1_pk           int NOT NULL,
   /* other columns */
)

CREATE TABLE Table2 (
       Table2_pk      int NOT NULL,
   /* other columns */
)

CREATE TABLE Table3 (
       Table1_pk           int NOT NULL,
       Table2_pk      int NOT NULL,
   /* other columns */
)

ALTER TABLE Table3
       ADD CONSTRAINT constA
              FOREIGN KEY (Table1_pk)
                             REFERENCES Table1
                             ON DELETE CASCADE

ALTER TABLE Table3
       ADD CONSTRAINT constB
              FOREIGN KEY (Table2_pk)
                             REFERENCES Table2
                             ON DELETE CASCADE

When a stored procedure inserts data in Table3, the values
of Table1_pk and Table2_pk

provided by the user should be valid, i.e. should exist in
Table1 and Table2.
I think that's stupid to use two SELECTs to perform a
validation before INSERTing the data,

because SQL Server will perform that search again to
enforce the constraints.

for error code 547
But than, how can I understand what column caused the
error? Table1_pk? Table2_pk? Both?
My specs require that I report every error on a special
log table: I could use FORMATMESSAGE to create an error
message and that store it, but how can I get the
parameters that the 547 string wants?
For the one who don't remember, the error message 547 is:

%ls statement conflicted with %ls %ls constraint '%.*ls'.
The conflict occurred in database '%.*ls', table '%.*ls'%
ls%.*ls%ls.

Can I retrieve the last (formatted!) error string reported
to the user by SQL Server? E.g.:

INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'constA'. The conflict occurred in
database 'prova', table 'Table3', column 'Table1_pk'.

Thank you very much,
Paolo

2. MMC error after sp3

3. Multiple stored procedure validation

4. newbie ? on dimensions in a MDXQuery

5. Conditional validation in a stored procedure

6. Single mail label for one record???

7. Rules / Data validation in a stored procedure

8. US - GA - ATLANTA *** ORACLE DBA ***

9. Calling a Java Stored Procedure from another Java Stored Stored Procedure

10. Stored Procedure calling Stored Procedure

11. Executing a Stored Procedure in a Stored Procedure and selecting on the result

12. Call a stored procedure from another stored procedure

13. Calling a stored procedure with parameters from another stored procedure