Pass NULL value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pass NULL value

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2014-02-12 : 01:31:09

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?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-02-12 : 01:44:01

It is possible to set NULL . This is a basic example , but demonstrates how to set NULL

UPDATE my_table SET name = NULL WHERE name = 'Tom';

You may need to build some logic around how the statement is created

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

stahorse
Yak Posting Veteran

86 Posts

Posted - 2014-02-12 : 02:03:16

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

this is what I have but it errors

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-12 : 04:00:54

[code]IF @NewFund_Id = '' ~~THEN~~ SET @NewFund_Id = null [/code]

alexsts
Starting Member

13 Posts

Posted - 2014-02-12 : 11:02:55

quote:
Originally posted by stahorse

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

this is what I have but it errors

First of all make sure that field which you attempt to insert/update NULL is null-able.Just look at the table design and you should see if field accept NULL as value.
And then, bit an old fashion, but it make your sp more readable:

IF (@NewFund_Id = '')
begin
set @NewFund_Id = null
end

But on top of your statement you have following :

quote:
SET @Product_Id = 'PIC2'
SET @Fund_Id = ''
SET @NewProduct_Id = 'PCCV'
SET @NewFund_Id = ''

If @NewFund_Id is always empty why would you even need verification

Just do
SET @NewFund_Id = null

Alex

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-12 : 15:31:10

TLDR;

Are you ID's really VARCHAR(50)?

You can set the value to NULL instead of empty string. You can use an IF..ELSE control structure. You can use a CASE expression. You can use the NULLIF function.. etc.. It really depends on how you are calling "it." Are you calling a stored procedure? If so, you can make the default value NULL and then not specify that parameter, etc.. Lots of options.

Here is a sample using the NULLIF function:

UPDATE Hosea_tempTable 
SET Product_Id = 'PCCV'
,Fund_Id = NULLIF(@NewFund_Id, '')
WHERE Product_Id = @Product_Id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources