SQL Server Performance

Parameter NOT NULL

Discussion in 'T-SQL Performance Tuning for Developers' started by BDRichardson, Jun 22, 2006.

  1. BDRichardson New Member


    I'm sure the answer to question I'm about to is NO, but I'd appreciate it if anyone would kindly confirm so.

    Well, is it possible to declare a Parameter for a Stored Procedure in SQL Server 2000 so that it may not be NULL, for example:

    CREATE PROC TestProcedure

    @Parameter INT NOT NULL



  2. Jack Vamvas Member

    The answer is no (there are defaults such as
    ALTER PROCEDURE TestProcedure
    @Parameter INT = NULL

    which will take the place of no value being passed.
    Usually best to handle it on the client call.
  3. BDRichardson New Member

    Much as I thought really, but I wanted to fully sure there was a way to ensure a Parameter was specified, as opposed to set to NULL.

    Many thanks for you prompt reassurance.
  4. spacemonkey New Member

    It is probably just extra work for you in this case. If you could use NOT NULL and someone tried, sql server would raise an error. If you check for a null value in the procedure, you can manually raise an error.

  5. Adriaan New Member

    Let's be precise about parameters and default values. The default only comes into play when the parameter is not referenced by the call that executes the stored procedure.

    So with our sproc ...

    CREATE PROCEDURE TestProcedure (@Parameter INT = 0) AS

    ... when you call this as:
    EXEC TestProcedure
    ... the default is applied, and the parameter has value 0 at the start of the procedure,

    ... but when you call this as:
    EXEC TestProcedure NULL
    ... the default is not applied, and the parameter has a null value at the start of the procedure.

    So even with a default, you still have to check for NULLs!
  6. BDRichardson New Member

    Adriaan - I agree am aware of your point, and agree with you.
    spacemonkey - Thanks for the tip, I will certainly consider implementing the idea when the problem next arises.

Share This Page