Interactively input in stored procedure

Question

Post reply

Interactively input in stored procedure

VRO

Newbie

Points: 9
More actions
October 5, 2005 at 10:11 am

#89567

Hi!
I need to build something like DialogBox in my stored procedure (without break it in two) such as :
CREATE PROCEDURE myproc
as
select * into tab1 from tab2
if exists( select * from tab1 where date_1 is null)
begin
-- HERE I NEED DECISION FROM USER:
-- MsgBOX ('Data_1 can't be empty! or ?')
-- IF -> DELETE FROM tab1 where date_1 is NULL
....
Any idea??

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Sorry,

Stored procedures do not work that way.

your pseudo code is not very clear. can you elaborate on your problem

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Also, those kind of questions must be asked before the user hits execute. If you need input after something execute, then you need to ask that question after the proc has finished running. Then call another procedure has needed.

VRO Newbie Points: 9 More actions · Answer 3

I mean any extend procedure (such as CMDSHELL ) that can return any value as input in my procedure.

Once more :

CREATE PROCEDURE myproc

as

select * into tab1 from tab2

if exists( select * from tab1 where date_1 is null)

begin

-- HERE I NEED DECISION FROM USER:

-- MsgBOX ('Date_1 can't be empty! "DELETE" or "UPDATE"?')

-- IF "DELETE" -> DELETE FROM tab1 where date_1 is NULL

-- IF "UPDATE" -> UPDATE tab1 set date_1=GetDate() where date_1 is NULL

...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Let me make this clear once and for all : THAT'S NOT THE WAY TO CODE.

What do you need to do exactly?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

The answer is absolutely not an interactive stored procedure. You could use a DTS or SSIS package with an InputBox in a script task, a web page, a .vbs script with ADO, or many other ways. Maybe another parameter with the answer, or two procedures where one assumes the answer is yes and the other assumes no.

But don't even think about putting something interactive in a stored procedure.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Create 2 procedures.

1st - to check if there are rows with NULL in DATA_1. Set @RETURN_VALUE for this SP according to the result.

You can just RETURN value of (select COUNT(*) ...WHERE DATA_1 IS NULL)

Than if there is no NULL values (@RETURN_VALUE = 0) - go straight to next SP to copy data.

If there are NULLs (@RETURN_VALUE > 0) ask client what to do and pass the answer to SP as a parameter.

It could be the same SP No.2 starting with something like this:

If @Action = 'DELETE'

DELETE FROM ...

WHERE DATA_1 IS NULL

IF @Action = 'UPDATE'

UPDATE....

WHERE DATA_1 IS NULL

IF there are no NULL rows it will do nothing with any value of @Action supplied.

_____________
Code for TallyGenerator