How can I catch a blank column value on SP input?

Question

Post reply

How can I catch a blank column value on SP input?

sg2000-429020

Ten Centuries

Points: 1106
More actions
January 28, 2008 at 1:37 pm

#186068

My SP checks for a non-Null @Firstname input with the statement
IF (@Firstname IS NULL) .........
and will reject it if null.
My question is: if the user enters a space in @Firstname, how can I catch and reject it? IS NULL will not catch a blank (empty) column (like a space character), will it?
Appreciate any helps on this. Thanks in advance.
sg2000

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

you could do something horrid like:

LTrim(IsNull(@Val, '')) <> ''

Which would catch NULLs and space only input.

Anyone got some better ones?

sg2000-429020 Ten Centuries Points: 1106 More actions · Answer 2

Thanks jeremy for the suggestion. Following your suggestion, I used

IF (@Name IS NULL OR @Name = '')

and found it working well.

sg2000

Lowell SSC Guru Points: 323518 More actions · Answer 3

Jeremy's method is better; your statement will not catch an error if a space ro multiple spaces are entered...only an empty string; Jeremy catches null, empty string, and spaces entered as data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sg2000-429020 Ten Centuries Points: 1106 More actions · Answer 4

I found both methods will catch null as well as single or multiple space; however, I'll use Jeremy's method as it is shorter. Thanks to all.

sg2000.

Mauve SSChampion Points: 11316 More actions · Answer 5

Ideally you should trim both leading and trailing spaces to avoid inserting an extra blank (or two) at the end of the passed in string.

Normally, all of this data scrubbing and validation would be done by the BLL (Business Logic Layer) -- which is the layer of code that calls the stored procedure. But that doesn't always happen.

The method I use is:

NULLIF(LTRIM(RTRIM(@p_last_name)), N'')

where @p_last_name is the input parameter -- which is of NVARCHAR data type (to handle all UNICODE characters).

This will scrub the data and convert any resulting empty string to NULL. Then you can pre-test for a NULL value or have a NOT NULL check constraint on the table which will prevent the inserting of any NULL values.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 6

sg2000 (1/29/2008)
Thanks jeremy for the suggestion. Following your suggestion, I used
IF (@Name IS NULL OR @Name = '')
and found it working well.
sg2000

If you want to add a further refinement to test for names that begin with a valid letter instead of something like # when you want a letter you can use something like the following example:

DECLARE @NameVar VARCHAR(20);

SET @NameVar = 'Bruce';

IF left(@NameVar,1) NOT BETWEEN 'A' AND 'Z'

GOTO FinishOff

PRINT 'Do some work here'

FinishOff:

I'm not a huge fan of GOTO, but it's useful sometimes. Also, you would want to know what characters are legal and possibly devise multiple tests.

Or do we finally have regular expressions in T-SQL and I just haven't gotten the news?

EDIT:

:blush: OK, no one smacked me for this, for which I'm grateful. We do, sorta, kinda, have regular expressions in T-SQL. Here[/url] is an article that describes how to implement them. Not for the faint of heart, but do-able.