kapil_kk (5/6/2013)
Sergiy (5/6/2013)
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working 🙂Is it correct?
Yes, it is correct.
In fact, "RETURN @pSTN " was incorrect.
In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.
RETURN as you used it must be used in scalar functions only.
Thanks for the suggestion 🙂
Actually, looking at the declaration of your procedure the following is how you should be invoking your procedure.
Procedure declaration (partial):
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 1001
,@pToStore int = 1004
,@pSTN VARCHAR(20) = 0 output
...
Invoked:
declare @pSTNval varchar(20);
exec dbo.BS_GetSTNValue @pFromStore = ??, @pToStore = ??, @pSTN = @pSTNVal OUTPUT -- The ?? are what ever values for FromStore and ToStore you are passing to the procedure
select @pSTNVal -- display value returned from procedure
This will eliminate the SELECT @pSTN you added to your procedure.