CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3410150
user3410150

Reputation: 61

How to check in stored procedure that value is null?

SQL Server Management Studio, stored procedure

I am passing customerName from Visual Studio and in the stored procedure I want to get the customerID.

Now I want to check if customerId is null, that means this is a new customer. If so, I want to insert that customer in table. Then I want to get the customer id, and want to store in TableB.

Here is sample code:

also tell How to perform 

if(@customerId == null)
{
   Insert customerDetails into CustomerTable;
   get CustomerId.
} //General workflow what i want.

For this I have written a SQL Server stored procedure 

create procedure Customers_AddData2    
    @CustomerName nvarchar(50) = null
As     
   --Get Customer ID
   declare @CustomerID numeric(18, 0)= (SELECT distinct [CustomerID] FROM [tblCustomerMaster] WHERE CustomerName = @CustomerName)

  --checking if customerId not found
  -- Here is some error, Not getting syntax 

  IF(@CustomerID IS NULL == true)  
  {
      INSERT INTO [tblCustomerMaster] ([CustomerName]) 
      VALUES (CustomerName)
  }

  -- Now get customer id
  @CustomerID numeric(18, 0)= (SELECT distinct [CustomerID] FROM [tblCustomerMaster] WHERE CustomerName = @CustomerName)

Upvotes: 1

Views: 4650

Answers (2)

Milen
Milen

Reputation: 8867

Just IF(@CustomerID IS NULL) is enough 

IF(@CustomerID IS NULL)
BEGIN
     --insert here..
      INSERT INTO [tblCustomerMaster]
     ([CustomerName]

    VALUES

    (CustomerName)
    SET @CustomerID = Scope_Identity() -- sql server syntax to get the ID after insert..
END 
ELSE
BEGIN
   -- update perhaps..
END

Upvotes: 5

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

You may try like this:

if (isnull(@customerID) == 1)
BEGIN
     --Your code
END 
ELSE
BEGIN
   -- code
END

Also change this line

declare @CustomerID numeric(18,0)= (SELECT distinct [CustomerID] FROM   [tblCustomerMaster]     where CustomerName = @CustomerName)

with this:

declare @CustomerID numeric(18,0)
(SELECT @CustomerID := [CustomerID] 
 FROM   [tblCustomerMaster]     
where CustomerName = @CustomerName)

Upvotes: 0

Related Questions