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 @PropertyDetail s nvarchar (6)
Select @PropertyDescri ption = PropertyDescrip tion from Property where
ApplicationID = @applicationid
If @PropertyDescri ption is not null or @PropertyDescri ption <> ''
Begin
If (select isnumeric(Prope rtyDescription) from Property where ApplicationID =
@applicationid) = 1
INSERT INTO #errors VALUES (1410,@Applicat ionID, 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.aspx/sql-server-general/200508/1

[Simon Hayes]

Re: Can't get SProc to work

If PropertyDescrip tion 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]

Re: Can't get SProc to work

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:[color=blue]
>If PropertyDescrip tion 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[/color]


--
Message posted via SQLMonster.com

http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200508/1

[Simon Hayes]

Re: Can't get SProc to work

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 @PropertyDescri ption nvarchar (6)

select @PropertyDescri ption = PropertyDescrip tion
from dbo.Property
where ApplicationID = @Applicationid

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

[Simon Hayes]

Re: Can't get SProc to work

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]

Re: Can't get SProc to work

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

Driesen


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