January 9, 2007 at 3:51 pm
This might not be supported, but I can't seem to find documentation one way or the other. Can anyone tell me if Stored Procedure parameters with the OUTPUT flag set can be used as input too?
i.e. (this may not be a working example, but I've shortened to get my point accross)
CREATE PROCEDURE myproc
@inout INT OUTPUT
AS
BEGIN
SET @inout= (SELECT MAX(myfield1) FROM mytable WHERE myfield2 = @inout);
END
January 9, 2007 at 10:12 pm
Yes, but only one at a time. You can't execute a procedure and specify the parameter as both an output and input parameter. So the limitation really is how you can call a procedure.
January 10, 2007 at 6:34 am
All params are at least input params. The value of the param is passed from the calling routine into the stored procedure.
Only those with the OUTPUT flag are output params. The value of the param is passed from the stored procedure to the calling routine when the stored procedure ends.
It's easy to try this out yourself just to make sure you understand it.
January 10, 2007 at 9:43 am
Thanks for you replies.
I had tried that and it worked as you described. I guess my question was not so much "will it work?", but "is it intended behaviour?" I was trying to find out where it is documented that "All params are at least input params".
I am testing an application that interacts with stored procedures and I just wanted to know what a user typically should expect. With Oracle, SP Params can be IN, OUT, or IN/OUT, but I guess for SQL Server it is just IN or IN/OUT.
January 10, 2007 at 11:43 am
The only way I ever use output parameters as input is when specifying a default for the parameter so that if the user does not want to use the output parameter, they can simply leave it out.
I think that for clarity's sake, it is best to use separate parameters for input and output.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply