Thursday, March 29, 2012

error executing sprocedure

Hello,
I've created one stored procedure that has one input parameter(number of days), if i don=B4t pass any value when executing this procedure sql reports one error. I would like to execute this procedure and don't pass the input paramenter to the stored procedure but if the number of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
--
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in which case you don't >have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message >news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards >
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regardssql

No comments:

Post a Comment