December 14, 2009 at 12:25 pm
Hi all,
I have an output parameter in my stored procedure:
alter PROCEDURE [dbo].[FinancialMembership]
(
@user-id varchar(10),
@Year int,
@CurrentYearPaid DECIMAL(5,2) OUTPUT
)
AS
SET NOCOUNT ON
--MY SELECT STATEMENT
RETURN
When using the SP
EXEC [dbo].[FinancialMembership] 'Test',2009
won't work because it is expecting @CurrentYearPaid as input. Why do I have to supply an input value to @CurrentYearPaid if i'm just going to write over it?
I also tried to just declare @CurrentYearPaid in the the procedure, but couldn't figure out how to make it an output variable:
DECLARE @CurrentYearPaid DECIMAL(5,2) OUTPUT
doesn't work. This is how I'd rather do it. Is there a way I should go about doing this?
Thanks,
Strick
December 14, 2009 at 12:49 pm
you simply need to add the output parameter in the call, you must provide an output variable to which the outpu should be stored
DECLARE @output decimal(5,2)
EXEC [dbo].[FinancialMembership] 'Test',2009, @output OUTPUT
December 14, 2009 at 2:20 pm
Hi,
Thanks for your response. Yeah this is what I do to get it working except I just throw values there like this:
EXEC [dbo].[FinancialMembership] 'Test',2009, 0.0
My question is more of a why. In both cases your sample and mine we're feeding in values in the stored procedure which are just going to get changed in the stored procedure. Why can't I only have two input parameters and declare my output parameters in that stored procedure?
Kinda like this:
alter PROCEDURE [dbo].[FinancialMembership]
(
@user-id varchar(10),
@Year int,
)
AS
SET NOCOUNT ON
DECLARE @CurrentYearPaid DECIMAL(5,2) OUTPUT --This of course does work, but I would rather
--declare the output parameter in the stored procedure
set @CurrentYearPaid = 1.25
--MY SELECT STATEMENT
RETURN
Also, on a side note how did you enclose your code to make it better readable in posts?
Thanks,
Strick
December 14, 2009 at 4:04 pm
stricknyn (12/14/2009)
Hi,Thanks for your response. Yeah this is what I do to get it working except I just throw values there like this:
EXEC [dbo].[FinancialMembership] 'Test',2009, 0.0
My question is more of a why. In both cases your sample and mine we're feeding in values in the stored procedure which are just going to get changed in the stored procedure. Why can't I only have two input parameters and declare my output parameters in that stored procedure?
Kinda like this:
alter PROCEDURE [dbo].[FinancialMembership]
(
@user-id varchar(10),
@Year int,
)
AS
SET NOCOUNT ON
DECLARE @CurrentYearPaid DECIMAL(5,2) OUTPUT --This of course does work, but I would rather
--declare the output parameter in the stored procedure
set @CurrentYearPaid = 1.25
--MY SELECT STATEMENT
RETURN
If you do not specify a default value for a parameter of stored proc, then you have to provide such parameter to a stored proc when you are executing it, like in other programming languages. If you want you can provide a default value, which will be used in case a parameter is not passed to the proc:
ALTER PROCEDURE [dbo].[FinancialMembership]
(
@user-id varchar(10),
@Year int,
@CurrentYearPaid DECIMAL(5,2) = 3 OUTPUT
)
AS
BEGIN
SET @CurrentYearPaid = ISNULL(@CurrentYearPaid, 0) + 2
END
In this example there is default value provided for the @CurrentYearPaid parameter and in this case you do not need to provide a value for it when calling such stored procedure. You can use both below mentioned commands
EXEC [dbo].[FinancialMembership] 'Test', 2009
EXEC [dbo].[FinancialMembership] 'Test', 2009, 5
In the first example it will print "5.00" (Default value of 3.00 + 2.00)
and in the second it will print "7.00" (Passed value 5.00 + 2.00)
If you want return the output the value to the calling context, you have to use a variable to which the value will be assigned with the OUTPUT keyword and the current value of the variable will be passed to the stored proc when executing it. If you do not include the OUTPUT keyword, the value will not be assigned to the outpu variable. As you can see below.
DECLARE @a decimal(5,2)
EXEC [dbo].[FinancialMembership] 'Test', 2009, @a
SELECT @a
EXEC [dbo].[FinancialMembership] 'Test', 2009, @a OUTPUT
SELECT @a
stricknyn (12/14/2009)
Also, on a side note how did you enclose your code to make it better readable in posts?
Thanks,
Strick
See the help on the left of the edit box when you are writing a post or reply to a post... there are "IFCode Shortcuts" which you can use. You can also insert them by the IFCode button in the toolbar.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply