SQLTeam.com | Weblogs | Forums

Setting ISNULL in a variable settings

Hello,
I have a variable setting like this:

DECLARE @BrokerCommissionPercNew FLOAT

SELECT @BrokerCommissionPercNew = BrokerCommissionPerc
FROM dbo.Policy WHERE IdQuotation = (SELECT TOP (1) IdQuotationOwner FROM dbo.Quotation where IdQuotation = @IdQuotation ORDER BY InsDate)

How can I change it in case I obtain NULL inside, and I'd like to set
@BrokerCommissionPercNew = 0 ?

Thank you in advance.

Luis

Put this after the select:
SET @BrokerCommissionPercNew = ISNULL(@BrokerCommissionPercNew,0)
The above will make it a 0 in both the cases: 1. the where condition is false 2.where condition is true but the column value is null..so make sure this is what you are looking for

1 Like

Thank you Rock, this could be enough.
Luis

...
SELECT @BrokerCommissionPercNew = ISNULL( BrokerCommissionPerc , 0)
FROM dbo.Policy ...
...

1 Like

Cannot work for my example Scott.
Thanks anyway.
L

hi

i am trying to get your query to work and then .. try to do the NULL issue
something is wrong !!!!!!!... with your query it seems
i created sample data for your SQL

thats why posting sample data will help
/*
Please post sample data ...for your questions
like this ...

drop table #sampledata
go

create table #sampledata
(ts datetime2,
item varchar(20),
sellerid varchar(20)
)
go

insert into #sampledata
values
('2019-09-05 12:02:55.533','b123 ','dfjk'),
('2019-09-03 12:02:55.533','b123 ','TV12'),
('2019-09-03 12:02:55.533','b123 ','uiop')
go

*/

please click arrow to the left for drop create data script
DROP TABLE dbo.policy 

go 

CREATE TABLE dbo.policy 
  ( 
     idquotation          INT, 
     brokercommissionperc FLOAT 
  ) 

go 

DROP TABLE dbo.quotation 

go 

CREATE TABLE dbo.quotation 
  ( 
     idquotation      INT, 
     idquotationowner VARCHAR(10), 
     insdate          DATE 
  ) 

go 

INSERT INTO dbo.policy 
SELECT 1, 
       10.25 

INSERT INTO dbo.quotation 
SELECT 1, 
       'har', 
       '2019-10-09' 

go 

INSERT INTO dbo.policy 
SELECT 2, 
       20.56 

INSERT INTO dbo.quotation 
SELECT 2, 
       'pra', 
       '2019-09-24' 

go 

SELECT * 
FROM   dbo.policy 

SELECT * 
FROM   dbo.quotation 

go
please click arrow to the left for SQL script
DECLARE @Brok ercommissionpercnew FLOAT 
DECLARE @IdQuotation INT = 2 


SELECT brokercommissionperc 
FROM   dbo.policy 
WHERE  idquotation = (SELECT TOP (1) idquotationowner 
                      FROM   dbo.quotation 
                      WHERE  idquotation = @IdQuotation 
                      ORDER  BY insdate)

Error message coming