insert NULL into column - 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

SQL Server 2005 Forums

Transact-SQL (2005)

insert NULL into column

Author

Topic

wqwqwq_
Starting Member

2 Posts

Posted - 2008-09-12 : 03:54:34

Hi, i have a problem a cannot find the answer to. I have to write a stored procedure that based on the given id parameter updates the row, or create a new one. Here is the procedure i did so far, logically its correct i think.

CREATE PROCEDURE [dbo].[rev_modul_version_updatebyid]
@id int = NULL,
@mv_code nvarchar(10),
@mv_release nvarchar(10),
@mv_md_id int,
@mv_create_user int = NULL,
@mv_modify_user int = NULL

AS
BEGIN
SET NOCOUNT ON;

IF (@id <= 0 OR @id IS NULL)
BEGIN
INSERT INTO
rev_modul_version
(mv_create_user, mv_create_date, mv_state)
VALUES
(@mv_create_user, getdate(), NULL)

SELECT
@id = SCOPE_IDENTITY()
END

UPDATE
rev_modul_version
SET
mv_code = @mv_code,
mv_release = @mv_release,
mv_md_id = @mv_md_id,
mv_modify_user = @mv_modify_user,
mv_modify_date = getdate()
WHERE
mv_id = @id

RETURN
@id
END

So basicly the idea is that if the @id is null, then first create the new row, than fill the rest of the columns after, if a proper @id is given, than just modify that one. The problem is that in the tables most of the columns dont allow null values, so i get an error message says "null values are not allowed". I could fix it with giving bogus data to the INSERT INTO statement something like this:

INSERT INTO
rev_modul_version
(mv_code, mv_release, etc, mv_create_user, mv_create_date, mv_state)
VALUES
(' ', ' ', etc, @mv_create_user, getdate(), NULL)

It works, but i dont like it im not an expert but i dont think this would be the proper way to do this. So my question is that can i somehow override this issue? Maybe a SET statement or something? Other thing i was thinking about is doing this with temporary tables but i wonder if this can be done this way, without the temporary tables.

Thanks.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-12 : 04:39:35

CREATE TABLE #TEMPTABLE
(
GUESTID INT IDENTITY(11784,1) NOT NULL,
GUESTNAME VARCHAR(100) DEFAULT 'UNKNOWN',
)

INSERT INTO #TEMPTABLE DEFAULT VALUES

ITS BETTER U SET A DEFAULT VALUE FOR COLUMN IF U DONT NEED TO INSERT BOGUS DATA. THIS WILL HELP.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 05:29:34

No need to update the recently inserted record.

CREATE PROCEDURE dbo.rev_modul_version_updatebyid
(
	@id int = NULL,
	@mv_code nvarchar(10),
	@mv_release nvarchar(10),
	@mv_md_id int,
	@mv_create_user int = NULL,
	@mv_modify_user int = NULL
)
AS

SET NOCOUNT ON

IF @id > 0
	UPDATE	rev_modul_version
	SET	mv_code = @mv_code,
		mv_release = @mv_release,
		mv_md_id = @mv_md_id,
		mv_modify_user = @mv_modify_user,
		mv_modify_date = getdate()
	WHERE	mv_id = @id
ELSE
	BEGIN
		INSERT	rev_modul_version
			(
				mv_create_user,
				mv_create_date,
				mv_state,
				mv_code,
				mv_release,
				mv_md_id
			)
		VALUES	(
				@mv_create_user,
				getdate(),
				NULL,
				@mv_code,
				@mv_release,
				@mv_md_id
			)

		SET	@id = SCOPE_IDENTITY()
	END

RETURN	@id

E 12°55'05.63"
N 56°04'39.26"

wqwqwq_
Starting Member

2 Posts

Posted - 2008-09-12 : 06:06:32

Thanks Peso, i will do it that way. But just out of curiosity, can it be done in your opinion any other way? It works fine in my case because i dont have that many columns, but lets say i have 20 columns, than i have to update each 20, than in the INSERT INTO section i have to add the almost same 20 values. Thats why i tried to do it the way i posted in the first place, it could've saved me quite a bit of typing, plus i thought its logical to create the row with the values that wont change (create_user, create_date), and than fill the rest.

So basically is there a way to put a NULL value into a field that doesnt allow NULL, by changing some security levels or something. Something similar to SET ARITHABORT ON/OFF, just not for the division by zero thing, but for inserting the NULL values.

Thanks a bunch though, your solution will be just fine for my work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 06:29:54

The typing is only once, right? When you write the code...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources