Get User-Input into a Stored Proc

Hi,
Is there any way to accomplish this (SQL Server 2000):
Client-App sends an Update to an SP.
The SP has to perform various actions, amongst one setting a FK.
Normally there will only be One FK possible per Update.
BUT
It can happens there are multiple FK's possible for One Update.
Under that condition the possible FK's should be sent back to the App where
the User can select One FK.
Once that FK is returned to the SP, the Procedure can continue it's actions.
I fear this is science fiction though I'd like to be sure :-)
TIA,
MichaelMichael,
A stored procedure has no way to request further input from the user. You
can, of course, write two stored procedures (1) figure out if all is well
and (2) do the work, then design your app to use the procedures.
RLF
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||You can of course do all of this - but not in a stored proc. Why would that
matter? Stored procs are not for UI code.
David Portas
SQL Server MVP
--
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||Hi David & Russel,
Thanks for your replies.
The reason I would like to implement this is that various Bit, DateTime & FK
fields have to be set accross various tables depending on certain Updates on
another table.
On itself this is pretty straight foreward, but in the App this procedure
can be started on many forms under various ways and conditions.
It is the Undoing of this operation that is tadious in the App. (so many
variations). It makes it easy to break the logic.
Having it all done by an Update-Trigger on that table, causing it to launch
various sp's, makes it all solid.
The only caveat is that there * can * be more then One FK and it's
impossoble for non human-logic to determine which to use.
Thus I was "hoping" there would be any means to have a user-interaction on
this level.
I think I will have to come up with an alternative.
Any way: thanks for the input guys!
Regads,
Michael
"David Portas" wrote:

> You can of course do all of this - but not in a stored proc. Why would tha
t
> matter? Stored procs are not for UI code.
> --
> David Portas
> SQL Server MVP
> --
> "Michael Maes" <michael.maes@.community.nospam> wrote in message
> news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
>
>|||> The only caveat is that there * can * be more then One FK and it's
> impossoble for non human-logic to determine which to use.
I've no idea what this means. The foreign keys on a table are fixed
unless you are executing DDL in your update. So how can there be any
doubt about which they are?
David Portas
SQL Server MVP
--|||I think I have expressed myself badly.
it's all about the Value of the FK to save.
To put it in a simplified example:
A workorder can consist of various tasks.
Each task is a certain day performed by a certain technician (the FK)
In another table (installation - statistics) you can see what date the last
visit was by which technician for what type of job, ...
Normally an orders' childrececords (tasks) is always performed by the same
technician. But sometimes more technicians are assigned to an order (each
having his own taskrow).
Since the Technician FK only holds one Value, the user has to decide which
technician to assign for "the last visit" because it's important for
follow-up & support to know who was the "most important" technician. It's
impossible for 'Code' to know which one to choose.
Hence the User-Input.
I hope this clarifies a bit my 'case'.
Regards,
Michael
"David Portas" wrote:

> I've no idea what this means. The foreign keys on a table are fixed
> unless you are executing DDL in your update. So how can there be any
> doubt about which they are?
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Michael,
Is it possible for you to provide a simplified table schema with sample
data to clarify it more? I am afriad one of the most possible resolution is
redesign the table structure or application structure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
Thanks for your reply.
For the moment I have worked around the issue, so I guess the 'Case is
closed' :-)
Thanks,
Michael
"Michael Cheng [MSFT]" wrote:

> Hi Michael,
> Is it possible for you to provide a simplified table schema with sample
> data to clarify it more? I am afriad one of the most possible resolution i
s
> redesign the table structure or application structure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Michael,
You are welcome and thanks for the update.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.