Can't get SProc to work

[Driesen via SQLMonster.com]

Hi

I can't seem to get this procedure to work correctly. Here's my code:

DECLARE @PropertyDetails nvarchar (6)
Select @PropertyDescription = PropertyDescription from Property where
ApplicationID = @applicationid
If @PropertyDescription is not null or @PropertyDescription <> ''
Begin
If (select isnumeric(PropertyDescription) from Property where ApplicationID =
@applicationid) = 1
INSERT INTO #errors VALUES (1410,@ApplicationID, 0, 'Y')
ELSE
INSERT INTO #errors values (1410, @ApplicationID, 0, 'N')
End

I am trying to bring up an error advising users not to capture alphabets in a
field called Property Description.
I need to bring up the error from the #ERRORS table with the rule number 1410.
My Syntax checks successful, but my error does not come up for the users. Am
I missing something?

Thanks for any help at all, guys.
Driesen Pillay
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200508/1

[Simon Hayes]

If PropertyDescription should only have numbers, then why is it a
character data type? What do you consider "numeric" to mean - what
digits/characters are allowed? You'll probably have to give some more
details of what you're trying to do and what your data looks like to
get a good answer - can you post a short script which others can
actually execute themselves?

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[Driesen via SQLMonster.com]

Hi Simon

Sorry about that. Even though I only want numbers in this field, I don't want
to stop the user from inputing alphabets (management red tape). Eg: If the
user enters "ERF 1234". I need to raise the error "Remove aplhabets". We have
an HTML interface with SQL running the actual background programming, so
unfortunately I can't supply you with a script. I know I didn't give you much
to go on, so I might scrap this rule, because it doesn't really make sense to
have it there. What would you recomend? Is this a waste of time?

Thanks for the help.
Driesen

Simon Hayes wrote:

If PropertyDescription should only have numbers, then why is it a
character data type? What do you consider "numeric" to mean - what
digits/characters are allowed? You'll probably have to give some more
details of what you're trying to do and what your data looks like to
get a good answer - can you post a short script which others can
actually execute themselves?

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200508/1

[Simon Hayes]

In general, this type of input validation would belong in the front
end, not the database; the database would have an integer column, and
the front end would make sure that only integer data is passed to it.

The problem with your current solution is that you have to load the
data into the database, check it, populate an error table, present the
contents of the error table to the user, wait for the user to enter new
values, and then repeat the process until the user gets it right. This
seems to be a lot of work compared to a simple input mask in your user
interface. Assuming that by "HTML interface" you mean ASP, PHP or
something similar, then this should be fairly easy.

But I appreciate that I don't know all the details of your application
or your environment, so you might try something like the code below
(untested).

Simon

declare @PropertyDescription nvarchar (6)

select @PropertyDescription = PropertyDescription
from dbo.Property
where ApplicationID = @Applicationid

if coalesce(@PropertyDescription, '') <> ''
and exists (
select *
from dbo.Property
where ApplicationID = @ApplicationID
-- find non-numeric characters
and rtrim(ltrim(PropertyDescription)) not like '%[^0-9]%'
)
INSERT INTO #errors VALUES (1410, @ApplicationID, 0, 'N')
ELSE
INSERT INTO #errors values (1410, @ApplicationID, 0, 'Y')
End

[Simon Hayes]

Oops - I think I put 'N' and 'Y' the wrong way round, but you should
see that from your data.

Simon

[Driesen via SQLMonster.com]

Thanks very much for the help, Simon. I will give that a try.
Thanks again.

Driesen
--
Message posted via http://www.sqlmonster.com