creating procedures

I have seen developers create procedures by declaring the variables and
others declaring the variables and setting them to null like below.
Is one way better that another?
CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
@.issuerId int output,
@.companyId int = NULL,
@.issuerName varchar(60) = NULL,
@.dateAdded datetime = NULL,
@.dateModified datetime = NULL,
@.statusDate datetime = NULL,
@.modifiedBy smallint = NULL ....
thanks
MeenalWhen declaring parameters of a procedure the declaration can include a
default value for each parameter. This is what your example shows. This way
the parameters become optional.
On the other hand, the declaration of a variable cannot include a default
value.
ML
http://milambda.blogspot.com/|||If a parameter to a stored procedure has a default assigned, the
parameter is optional when called. If no default is defined, the
parameter is required, and if not provided it will fail:
exec sp_helpindex
Server: Msg 201, Level 16, State 4, Procedure sp_helpindex, Line 0
Procedure 'sp_helpindex' expects parameter '@.objname', which was not
supplied.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Jun 2006 00:48:47 -0400, "meenal"
<meenal_dhody@.hotmail.com> wrote:

>I have seen developers create procedures by declaring the variables and
>others declaring the variables and setting them to null like below.
>Is one way better that another?
>CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>thanks
>Meenal
>|||IF they are defined in the CREATE statement, what 'looks' like variables are
really known as 'Parameters'. Variables are defined in the stored procedure
body with the DECLARE statement.
It's often easy to confuse Parameters and Variables.
When Parameters are provided values when they are defined, two things are no
w possible. First, it is not necessary to provide a value for that Parameter
when the stored procedure is executed. Second, it may be that a particular
value 'should' be used it the stored procedure if no value of offered.
In Query Analyzer, try the following example:
CREATE PROCEDURE dbo.MyTest
( @.CheckValue int = 0
, @.DateInput datetime = NULL
)
AS
IF @.DateInput IS NULL
SET @.DateInput = getdate()
IF @.CheckValue = 0
PRINT 'Choice Number 0 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE IF @.CheckValue = 1
PRINT 'Choice Number 1 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE
PRINT 'Choice Number ' + cast( @.CheckValue AS varchar(5) ) + ' Selected on '
+ cast( @.DateInput AS varchar(11) )
GO
EXECUTE dbo.MyTest
EXECUTE dbo.MyTest @.CheckValue = 1
EXECUTE dbo.MyTest @.CheckValue = 25, @.DateInput = 'jan 1 2005'
--(Note: it is a good practice to use name=value pairs when calling stored p
rocedures and passing parameters.
--It makes your intention very clear, and protects against future changes to
the stored procedure.)
-- Clean up when finished
--DROP PROCEDURE MyTest
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"meenal" <meenal_dhody@.hotmail.com> wrote in message news:uathBVamGHA.2112@.TK2MSFTNGP04.phx
.gbl...
>I have seen developers create procedures by declaring the variables and
> others declaring the variables and setting them to null like below.
>
> Is one way better that another?
>
> CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>
> thanks
>
> Meenal
>
>