convert "" to null for column with not allow null

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

SQL Server 2005 Forums

Transact-SQL (2005)

convert "" to null for column with not allow null

Author

Topic

veronika.np
Starting Member

29 Posts

Posted - 2011-05-27 : 09:41:52

hi friends
i have a question.i have a table with some column that they can not allow null.but i have a sp and in this my sp i want to insert value for some columns.for columns that they are can not allow null i pass ""(empty).i want to convert them to null.how can i do it?
please help me.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 09:45:51

you can use nullif function. For example,


insert into YourTable values (nullif(col1value,''), nullif(col2value,''));

This assumes that the column is a character data type. If it is a numeric data type, you would need to use nullif(col1,0) (or whatever value you would be receiving to indicate that it is null).

veronika.np
Starting Member

29 Posts

Posted - 2011-05-27 : 10:00:53

i write this
insert into[user]([pass],[year],[month],[day] )values(nullif(pass,''),'5','5','5')

but it has this error:
Msg 128, Level 15, State 1, Line 1
The name "pass" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

i want to replace value of column pass with null.

veronika.np
Starting Member

29 Posts

Posted - 2011-05-27 : 11:37:56

i test it again and it has this error:
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'id', table 'test.dbo.test'; column does not allow nulls. INSERT fails.

please help me.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 12:26:29

The syntax of the insert statement is as follows:


insert into 
    [user] -- <-- this is the name of the table.
    ([pass],[year],[month],[day]) -- <-- these are the columns in the table. 
values
    (nullif(pass,''),'5','5','5') -- <-- these are the values you want to insert.

In the section where you are supposed to provide values, you have "nullif(pass,'')". But, pass is a column in the table. That is why it is complaining.

Without seeing the insert statement you are using, it is hard for me to say what you should do. The following would work, if column pass is of data type character.

declare @myPass varchar(31);
set @myPass = null;

insert into 
    [user] -- <-- this is the name of the table.
    ([pass],[year],[month],[day]) -- <-- these are the columns in the table. 
values
    (nullif(@myPass,''),'5','5','5') -- <-- these are the values you want to insert.

Now @myPass is null, so the nullif function inserts an empty space into the table.

If you look up Brett's blog here : http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx it will tell you how to get the table schema and sample data to post. If you can do that, it would be easier to spot what might need to be changed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources