CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Accepting NULLS into Stored Procedures

I've got the below stored procedure that is very simple, but it allows NULL values to be passed in.

I'm just wondering what best practice is here, and how to deal with NULLs in cases such as this?

ALTER PROCEDURE [dbo].[spGetClient]
    @ClientID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        ......
    FROM
        Client
    WHERE
        ClientID = @ClientID

Upvotes: 0

Views: 1642

Answers (2)

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

You can raise a custom error:

ALTER PROCEDURE [dbo].[spGetClient]
    @ClientID int
AS
BEGIN
    SET NOCOUNT ON;

    if @ClientID is null 
    BEGIN
          raiserror('The value for ClientID should not be null', 15, 1)
          return;
    END
    SELECT
        ......
    FROM
        Client
    WHERE
        ClientID = @ClientID
END

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269813

The simplest thing would be to just declare the argument as NOT NULL:

ALTER PROCEDURE [dbo].[spGetClient] (
    @ClientID int NOT NULL
)
. . .

However, that is not quite allowed for all stored procedures.

Your stored procedure is fine. If a NULL value is passed in, then it will return no rows. This seems reasonable, because ClientId is probably never NULL in the Client table, so I don't see a problem.

Upvotes: 0

Related Questions