Altering a stored procedure

I am modifying a stored procedure to perform a check before updating the
table. I wanted to run tnis if else stmt to see if there is a better way to
perform the task.
The original sp generates a account number for a customer and updates the
table with the new account number.
I want to add a IF stmt to check if the account number already exists before
generating the account number, Print a message that the account # already
exsits and it will keep checking until a new account# appears.
The code:
IF (SELECT @.accountnumber <> @.accountnumberinput) FROM tablekeys
WHERE keyname = 'accountnumber')
BEGIN
UPDATE tablekeys SET @.accountnumberinput = currentvalue = (currentvalue /
2) +
((currentvalue % 2 + ((currentvalue / 8) % 2)) % 2) * POWER(2, 30)
WHERE keyname = 'accountnumber'
SELECT @.accountnumber = db.rcutil_inttobasex(@.accountnumberinput,
'0123456789BCDFGHJKLMNPQRSTVWXYZ')
END
ELSE
PRINT 'Account Number already exists, trying again'Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:

> I am modifying a stored procedure to perform a check before updating the
> table. I wanted to run tnis if else stmt to see if there is a better way t
o
> perform the task.
> The original sp generates a account number for a customer and updates the
> table with the new account number.
> I want to add a IF stmt to check if the account number already exists befo
re
> generating the account number, Print a message that the account # already
> exsits and it will keep checking until a new account# appears.
>
This is not possible with a stored procedure because a stored proc is not
made for interactive communication. The stored proc can only send back
something (a value, a result set, an error message) to the calling
application, the rest must be done by the application and not by the stored
procedure.
So you can do the check and if it fails you can send back the message to
the calling application, then the calling application shows the message to
the user, the user enters a new account number and the application calls
the stored proc again with the new account number and so on...
By the way, your IF looks wrong for me, is this working? I think not.
For example, if i want to check if the @.newaccountnumber exists in the
accounttable, i would write the statement this way:
IF exists(select * from accounttable where accountnumber =
@.newaccountnumber) begin
raiserror('account number exists',16,1)
return -1
end
...
But if i am wrong, forget this sample :-))
bye, Helmut|||The user doesn't enter the account number. The stored procedure generates th
e
account number and assigns it the customer or user by updating the table.
I was just displaying a message but it is not necessary. I just wanted to
perform a check within the procedure to check the account prior to generatin
g
the new account number.
Therefore, there is nothing entered by the user or application for
interaction with the sp.
Would I still use your sample?
"Helmut Woess" wrote:

> Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
>
> This is not possible with a stored procedure because a stored proc is not
> made for interactive communication. The stored proc can only send back
> something (a value, a result set, an error message) to the calling
> application, the rest must be done by the application and not by the store
d
> procedure.
> So you can do the check and if it fails you can send back the message to
> the calling application, then the calling application shows the message to
> the user, the user enters a new account number and the application calls
> the stored proc again with the new account number and so on...
> By the way, your IF looks wrong for me, is this working? I think not.
> For example, if i want to check if the @.newaccountnumber exists in the
> accounttable, i would write the statement this way:
> IF exists(select * from accounttable where accountnumber =
> @.newaccountnumber) begin
> raiserror('account number exists',16,1)
> return -1
> end
> ...
> But if i am wrong, forget this sample :-))
> bye, Helmut
>|||Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:

> The user doesn't enter the account number. The stored procedure generates
the
> account number and assigns it the customer or user by updating the table.
> I was just displaying a message but it is not necessary. I just wanted to
> perform a check within the procedure to check the account prior to generat
ing
> the new account number.
> Therefore, there is nothing entered by the user or application for
> interaction with the sp.
> Would I still use your sample?
Hm, okay, sorry, my english is not the best, propably i missunderstand you.
And i cannot find out, why there is @.accountnumberinput, if nothing is
entered by user or application. So i don't know how you will generate a
unique accountnumber if the first generated number is not unique..?
I would need more input because i don't understand your question :-(
bye, Helmut|||It is grapping that value from another table.
When a new user is added via the Web UI, a new account number generated and
added to the table along with the customer information that was entered by
the user. The account number is not entered by the user, the system assigns
this number via the store procedure.
Currently, when a new user is added under an exisitng account, that user
shares the same account number. We do not want this to happen. We want each
user, rather with the same company or under the same account name to have
their own account number.
Therefore, I wanted to alter the existing stored procedure to add a check
clause. If the new user that is being added and the system tries to assigned
an existing account number to the new user, I want a flag or check clause to
not assigned the user the same acct # but generated a new one and assigned i
t
to the new user.
I hope that makes more sense.
Actually, I think I need to perform this check in another stored procedure
that is creating the account information. I will post that code in a few
minutes. Thanks
"Helmut Woess" wrote:

> Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
>
> Hm, okay, sorry, my english is not the best, propably i missunderstand you
.
> And i cannot find out, why there is @.accountnumberinput, if nothing is
> entered by user or application. So i don't know how you will generate a
> unique accountnumber if the first generated number is not unique..?
> I would need more input because i don't understand your question :-(
> bye, Helmut
>sql