How to Restrict Null as Parameter to Stored Procedure SQL Server

How to restrict NULL as parameter to stored procedure SQL Server?

Parameter validation is not currently a feature of procedural logic in SQL Server, and NOT NULL is only one possible type of data validation. The CHAR datatype in a table has a length specification. Should that be implemented as well? And how do you handle exceptions? There is an extensive, highly developed and somewhat standards-based methodology for exception handling in table schemas; but not for procedural logic, probably because procedural logic is defined out of relational systems. On the other hand, stored procedures already have an existing mechanism for raising error events, tied into numerous APIs and languages. There is no such support for declarative data type constraints on parameters. The implications of adding it are extensive; especially since it's well-supported, and extensible, to simply add the code:

IF ISNULL(@param) THEN
    raise error ....
END IF

The concept of NULL in the context of a stored procedure isn't even well-defined especially compared to the context of a table or an SQL expression. And it's not Microsoft's definition. The SQL standards groups have spent a lot of years generating a lot of literature establishing the behavior of NULL and the bounds of the definitions for that behavior. And stored procedures isn't one of them.

A stored procedure is designed to be as light-weight as possible to make database performance as efficient as possible. The datatypes of parameters are there not for validation, but to enable the compiler to give the query optimizer better information for compiling the best possible query plan. A NOT NULL constraint on a parameter is headed down a whole nother path by making the compiler more complex for the new purpose of validating arguments. And hence less efficient and heavier.

There's a reason stored procedures aren't written as C# functions.

How to declare a not null parameter into Store procedure in a right way?

you can try it like the below. Null and Not Null does not support in Parameter of Store Procedure

Create procedure dbo.Update_Lager (@productName varchar (50)='', 
@info varchar(30)=null, @amount int=null, @Preis float=null)
as begin
    if (@productName is not null)
        begin 
            if  (@info is not null and @amount is not null and @Preis is not null)
        begin
            update Artikel set Info = @info, Anzahl = @amount, Preis = @Preis where Produktbezeichnung = @productName
        end
    else if (@info is not null)
        begin
            update Artikel set Info = @info where Produktbezeichnung = @productName
        end
    else if (@amount is not null)
        begin
            update Artikel set Anzahl = @amount where Produktbezeichnung = @productName
        end
    else if (@Preis is not null)
        begin
            update Artikel set Preis = @Preis where Produktbezeichnung = @productName
        end
    end
    else
        begin
            print ('You must enter the product name!')
        end
end

Stored procedure doesn't allow null parameters even the parameter represent accept nulls?

I just found that I can set default values for the parameter in the stored procedure:

ALTER proc [dbo].[spAddCustomer]
@cuName varchar(50)=null,
@cuAddress varchar(50)=null,
@cuMobile varchar(50)= null,
@cuImage image= null,
@cityId int= null,
@exist int output

And this solved my problem!
This is helpful specifically with null images from the PictureBox, since I have a helper method that checks for empty strings.

SQL Server stored procedure Nullable parameter

It looks like you're passing in Null for every argument except for PropertyValueID and DropDownOptionID, right? I don't think any of your IF statements will fire if only these two values are not-null. In short, I think you have a logic error.

Other than that, I would suggest two things...

First, instead of testing for NULL, use this kind syntax on your if statements (it's safer)...

    ELSE IF ISNULL(@UnitValue, 0) != 0 AND ISNULL(@UnitOfMeasureID, 0) = 0

Second, add a meaningful PRINT statement before each UPDATE. That way, when you run the sproc in MSSQL, you can look at the messages and see how far it's actually getting.

How to pass null to SQL Server stored procedure param with NON-NULL default value

null is a valid value to pass, and it won't be overridden by the default parameter value. I can't repro this behavior in T-SQL or ADO.NET.

EG for

create or alter procedure [dbo].[sp_UpdateSearchValues]  
    @pAuthID nvarchar(255),  
    @pgKey int,   
    @pSearch01 nvarchar(255) = 'BTR:NOSEARCHUPDATE',  
    @pSearch02 nvarchar(255) = 'BTR:NOSEARCHUPDATE',  
    @pSearch30 nvarchar(255) = 'BTR:NOSEARCHUPDATE' 
as
begin
    select @pSearch01 search01
end

then in .NET

using (var con = new SqlConnection("server=.;database=tempdb;integrated security=true"))
{
   con.Open();

   SqlCommand cmd = con.CreateCommand();
   cmd.CommandText = "sp_UpdateSearchValues";
   cmd.CommandType = System.Data.CommandType.StoredProcedure;

   var pAuthID = cmd.Parameters.Add("@pAuthID", SqlDbType.NVarChar,255 );
   var pgKey = cmd.Parameters.Add("@pgKey", SqlDbType.Int);
   var pSearch01 = cmd.Parameters.Add("@pSearch01", SqlDbType.NVarChar, 255);

   pAuthID.Value = "a";
   pgKey.Value = 1;
   pSearch01.Value = DBNull.Value;

   var r = cmd.ExecuteScalar();

   Console.WriteLine($"{r} {r.GetType().Name}");

   Console.ReadKey();
}

Outputs

DBNull

But it looks like this is a simple case of null comparison and 3-valued logic.

Consider:

declare @pSearch01 nvarchar(200) = null
select CASE WHEN @pSearch01 != 'BTR:NOSEARCHUPDATE' then 1 else 0 end

What does that return? Is null != 'BTR:NOSEARCHUPDATE' a true statement? No it's not.

Handling NULL in a stored procedure parameter

You can write the logic as:

select *
from table
where (Vendorname = @Vendor) OR (@Vendor IS NULL)

One caution: This may not be as optimized as your version, if you have an index on Vendorname.

Execute stored procedure with a nullable parameter from SQL server management studio

Turns out the syntax is fine for all the SPs I tried later. And there is no performance issue. The problem with uspUser_GetUserDetails 'LastName','FirstName','UserId',null,'DistrictId' before is probably related to "Timeout expired".

Alternatively, EXEC [dbo].[uspSPName]
@LastName = N'LastName'
@FirstName = N'',
@UserId = 0,
@dtDate = NULL,
@DistrictId = 1

How can I use optional parameters in a T-SQL stored procedure?

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END

Stored Procedure NULL Parameter

No, you can't detect how @Sort became NULL. If your goal is to capture when it is explicitly set versus it being set by the default, I would suggest using a different default value (maybe one that wouldn't normally be used, like -1). Then you can assume that if @Sort is NULL, it was explicitly passed in, but if it is -1, you know it was set by default.

---

Related Topics