CODEWITHSUNDEEP
sqlsql-serverstored-procedures
JAX
JAX

Reputation: 1620

Calling a series of stored procedures sequentially SQL

Is it possible (using only T-SQL no C# code) to write a stored procedure to execute a series of other stored procedures without passing them any parameters?

What I mean is that, for example, when I want to update a row in a table and that table has a lot of columns which are all required, I want to run the first stored procedure to check if the ID exists or not, if yes then I want to call the update stored procedure, pass the ID but (using the window that SQL Server manager shows after executing each stored procedure) get the rest of the values from the user.

When I'm using the EXEC command, I need to pass all the parameters, but is there any other way to call the stored procedure without passing those parameter? (easy to do in C# or VB, I mean just using SQL syntax)

Upvotes: 0

Views: 859

Answers (3)

Gary Walker
Gary Walker

Reputation: 9134

I think you are asking "can you prompt for user input in a sql script?". No not really.

You could actually do it with seriously hack-laden calls to the Windows API. And it would almost certainly have serious security problems.

But just don't do this. Write a program in C#, VB, Access, Powerscript, Python or whatever makes you happy. Use an tool appropriate to the task.

-- ADDED

Just so you know how ugly this would be. Imagine using the Flash component as an ActiveX object and using Flash to collect input from the user -- now you are talking about the kind of hacking it would be. Writing CLR procs, etc. would be just as big of a hack.

You should be cringing right now. But it gets worse, if the TSQL is running on the sql server, it would likely prompt or crash on the the server console instead of running on your workstation. You should definitely be cringing buy now.

If you are coming from Oracle Accept, the equivalent in just not available in TSQL -- nor should it be, and may it never be.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

Right after reading your comment now I can understand what you are trying to do. You want to make a call to procedure and then ask End User to pass values for Parameters.

This is a very very badddddddddddddddddddd approach, specially since you have mentioned you will be making changes to database with this SP.

You should get all the values from your End Users before you make a call to your database(execute procedure), Only then make a call to database you Open a transaction and Commit it or RollBack as soon as possible and get out of there. as it will be holding locks on your resources.

Imagine you make a call to database (execute sp) , sp goes ahead and opens a transaction and now wait for End user to pass values, and your end user decides to go away for a cig, this will leave your resources locked and you will have to go in and kill the process yourself in order to let other user to go and use database/rows.

Solution

At application level (C#,VB) get all the values from End users and only when you have all the required information, only then pass these values to sp , execute it and get out of there asap.

Upvotes: 1

Steve
Steve

Reputation: 5545

You can specify the parameters by prefixing the name of the parameter with the @ sign. For example, you can call an SP like this:

EXEC MyProc @Param1='This is a test'

But, if you are asking if you can get away with NOT providing required parameters, the answer is NO. Required is required. You can make them optional by providing a default value in the declaration of the SP. Then you can either not pass the value or call it like this:

EXEC MyProc @Param1=DEFAULT
--OR
EXEC MyProc DEFAULT

Upvotes: 1

Related Questions