SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pass NULL value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 02/12/2014 :  01:31:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2075 Posts

Posted - 02/12/2014 :  01:44:01  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 02/12/2014 :  02:03:16  Show Profile  Reply with Quote
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

Edited by - stahorse on 02/12/2014 02:06:52
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 02/12/2014 :  04:00:54  Show Profile  Reply with Quote
IF @NewFund_Id = '' THEN SET @NewFund_Id = null
Go to Top of Page

alexsts
Starting Member

USA
13 Posts

Posted - 02/12/2014 :  11:02:55  Show Profile  Reply with Quote
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

Edited by - alexsts on 02/12/2014 11:09:39
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/12/2014 :  15:31:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000