Useing TSQL to make a error to tell people what format they need to enter the data in.

Question

Useing TSQL to make a error to tell people what format they need to enter the data in.

hawaii_nexus

SSC Enthusiast

Points: 190
More actions
March 13, 2008 at 6:21 pm

#62953

i have end-users that are spending all day trying to enter data in the wrong format (123321233) when they have to enter the data as (123-32-1233) how do i create a error for them to tell them to enter it this way?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

The answer depends a lot on how they are entering the data in the first place. Is there a custom frontend app or fancy gui? If there is, then that is where the cleaning should happen. I've worked with a few apps that do not deal well or at all with server error messages.

That said, here are 2 simple TSQL ways of stopping hyphens from getting stored:

declare @myvar varchar(12)

set @myvar = '123-32-1233'

--set @myvar = '123321233'

if @myvar like '%-%'

RAISERROR('Error: No hyphens allowed', 0, 1) WITH NOWAIT

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

create table #test (myvar varchar(12))

alter table #test add constraint NoHyphens CHECK (myvar not like '%-%')

insert into #test select '123-32-1233' -- gets rejected

insert into #test select '123321233'

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

Validating in the client application is one option. For example if this is an ASP.NET application then using the RegularExpressionValidator would allow the input to be checked and provide a way to display acceptable input if validation doesn't pass. (ASP.NET RegularExpressionValidator Control)

On the SQL side you could add a check constraint on the column. That still requires code on the client side to interpret and respond to the error.

For example:

ALTER TABLE myTable ADD CONSTRAINT ck_myCol CHECK (myCol LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')

This article might interest you as well: http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/

Edit: fixed URL.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

The real answer is that you shouldn't store the dashes... that's formatting and it really has no place in the database. I'd make the app strip out the dashes and allow folks to enter just the digits. Only thing I'd check for is is it all digits and is it exactly 9 characters?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)