February 11, 2014 at 11:31 pm
Hi
I would like to know if it's possible to pass NULL value to the database. I want to update a column, a parameter has to be passed in to do so, i.e @value = '123'. but if there were no parameter value, i.e @value = '', I want to the value in the database to be NULL. I don't want to pass (''). can this be achieved?
February 11, 2014 at 11:49 pm
you can assign 'null' to the parameter, so it will update the column with null
like this
Declare @param as varchar(20)
Declare @table1 table ( id int identity(1,1), name varchar(20))
insert into @table1 (Name)
select 'ABC' union all
select 'DEF' union all
select 'GHI'
SET @param = null
update @table1
set name = @param
select * from @table1
February 12, 2014 at 12:07 am
This is what I have but it errors
DECLARE @Product_Id VARCHAR(50), @Fund_Id VARCHAR(50),
@NewFund_Id VARCHAR(50),@NewProduct_Id VARCHAR(50)
SET @Product_Id = 'PIC2'
SET @Fund_Id = ''
SET @NewProduct_Id = 'PCCV'
SET @NewFund_Id = ''
IF @NewFund_Id = '' THEN @NewFund_Id = null
UPDATE Hosea_tempTable
SET Product_Id = 'PCCV'
,Fund_Id = @NewFund_Id
WHERE Product_Id = @Product_Id
select * from Hosea_tempTable
February 12, 2014 at 12:35 am
You may try this
UPDATE Hosea_tempTable
SET Product_Id = CASE
WHEN @NewProduct_Id <> '' THEN @NewProduct_Id
ELSE NULL
END
,Fund_Id = CASE
WHEN @NewFund_Id <> '' THEN @NewFund_Id
ELSE NULL
END
WHERE Product_Id = @Product_Id
February 12, 2014 at 1:03 am
hoseam (2/12/2014)
This is what I have but it errorsDECLARE @Product_Id VARCHAR(50), @Fund_Id VARCHAR(50),
@NewFund_Id VARCHAR(50),@NewProduct_Id VARCHAR(50)
SET @Product_Id = 'PIC2'
SET @Fund_Id = ''
SET @NewProduct_Id = 'PCCV'
SET @NewFund_Id = ''
IF @NewFund_Id = '' THEN @NewFund_Id = null
UPDATE Hosea_tempTable
SET Product_Id = 'PCCV'
,Fund_Id = @NewFund_Id
WHERE Product_Id = @Product_Id
select * from Hosea_tempTable
THEN is not for IF it used in CASE Statement
2ndly, i usually never liked @NewFund_Id = '' because application can give all kind of parameters like empty spaces or '' while copy pasting in the field which user entry usually do.
i prefer it like this
IF Len(ltrim(@NewFund_Id)) = 0
begin
@NewFund_Id = null
end
hope it helps
February 12, 2014 at 2:05 am
1) You have to remove THEN from the IF statement
2) To assign a value to a variable you have to use SET statement
So, replace this line
IF @NewFund_Id = '' THEN @NewFund_Id = null
with this one
IF @NewFund_Id = '' SET @NewFund_Id = null
___________________________
Do Not Optimize for Exceptions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply