|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > StoredProc and NUll
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
StoredProc and NUll
|
|
| M O J O 2002-11-10, 9:23 am |
| Hi,
I can't pass Null as a parameter, here's my table:
CREATE TABLE [dbo].[Customer] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50)
) ON [PRIMARY]
GO
Here's my StoredProc:
CREATE PROCEDURE dbo.InsertName @Name nvarchar(50) AS
INSERT INTO Customer (Name) VALUES (@Name)
GO
I can do this: InsertName "MyName"
I can't do this InsertName Null
I get this error:
Server: Msg 515, Level 16, State 2, Procedure InsertName, Line 2
Cannot insert the value NULL into column 'Name', table
'MyDB.dbo.Customer'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I need to be able to insert Null.
What am I doing wrong?
Thanks!!
M O J O
| |
| Dejan Sarka 2002-11-10, 9:23 am |
| Hi!
SQL Server's default when creating a table is NOT NULL for all columns. So,
even if you did not mention NOT NULL for the name colimn, you have it. If
you need NULL values, ad the keyword NULL when creating the table.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"M O J O" < mojo@____no____spa_m___newwebs
olutions.dk> wrote in message
news:3DCE6E1E. 3080201@____no____spa_m___neww
ebsolutions.dk...
> Hi,
>
> I can't pass Null as a parameter, here's my table:
>
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50)
> ) ON [PRIMARY]
> GO
>
>
> Here's my StoredProc:
>
> CREATE PROCEDURE dbo.InsertName @Name nvarchar(50) AS
> INSERT INTO Customer (Name) VALUES (@Name)
> GO
>
>
> I can do this: InsertName "MyName"
>
> I can't do this InsertName Null
>
> I get this error:
>
> Server: Msg 515, Level 16, State 2, Procedure InsertName, Line 2
> Cannot insert the value NULL into column 'Name', table
> 'MyDB.dbo.Customer'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
>
> I need to be able to insert Null.
>
> What am I doing wrong?
>
> Thanks!!
>
> M O J O
>
>
>
| |
| Uri Dimant 2002-11-10, 9:23 am |
| MOJO
Check SET ANSI_NULL_DFLT_ON
M O J O < mojo@____no____spa_m___newwebs
olutions.dk> wrote in message
news:3DCE6E1E. 3080201@____no____spa_m___neww
ebsolutions.dk...
> Hi,
>
> I can't pass Null as a parameter, here's my table:
>
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50)
> ) ON [PRIMARY]
> GO
>
>
> Here's my StoredProc:
>
> CREATE PROCEDURE dbo.InsertName @Name nvarchar(50) AS
> INSERT INTO Customer (Name) VALUES (@Name)
> GO
>
>
> I can do this: InsertName "MyName"
>
> I can't do this InsertName Null
>
> I get this error:
>
> Server: Msg 515, Level 16, State 2, Procedure InsertName, Line 2
> Cannot insert the value NULL into column 'Name', table
> 'MyDB.dbo.Customer'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
>
> I need to be able to insert Null.
>
> What am I doing wrong?
>
> Thanks!!
>
> M O J O
>
>
>
| |
| John Gooch 2002-11-10, 1:23 pm |
|
Create your tables as follows :
CREATE TABLE [dbo].[Customer] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
Regards,
M O J O wrote:
> Hi,
>
> I can't pass Null as a parameter, here's my table:
>
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50)
> ) ON [PRIMARY]
> GO
>
>
> Here's my StoredProc:
>
> CREATE PROCEDURE dbo.InsertName @Name nvarchar(50) AS
> INSERT INTO Customer (Name) VALUES (@Name)
> GO
>
>
> I can do this: InsertName "MyName"
>
> I can't do this InsertName Null
>
> I get this error:
>
> Server: Msg 515, Level 16, State 2, Procedure InsertName, Line 2
> Cannot insert the value NULL into column 'Name', table
> 'MyDB.dbo.Customer'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
>
> I need to be able to insert Null.
>
> What am I doing wrong?
>
> Thanks!!
>
> M O J O
>
>
>
|
|
|
|
|