"NOT NULL" problem in a Stored Procedure

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

"NOT NULL" problem in a Stored Procedure

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-08-05 : 05:47:32

Hello,
I have problem inserting records through my stored procedure.

This is the procedure:


CREATE PROCEDURE InsertProducts 

	@ProductKey VARCHAR(25), 
	@ProductName VARCHAR(25), 
	@ProductDescription VARCHAR(125), 
	@ProductPrice INT, 
	@ProductQtyOnStock INT
AS
	
	DECLARE @ProductID SMALLINT
	
		-- Insert Values into Products
	BEGIN TRANSACTION 
		INSERT INTO dbo.Products (ProductKey, Productname, ProductDescription, ProductPrice, ProductQtyOnStock)
		VALUES					(@ProductKey, @ProductName, @ProductDescription, @ProductPrice, @ProductQtyOnStock)
	COMMIT
	
		-- Insert message into ProcedureLog
		INSERT INTO dbo.ProcedureLog (ProcMessage)
		VALUES ('ProductKey: ' + @ProductKey + ' with ProductID: ' + CONVERT(VARCHAR(25), @ProductID) + ' inserted successfully!')

This is the ProcedureLog-table:

-- Created the table ProcedureLog
CREATE TABLE ProcedureLog
	(ProcID SMALLINT IDENTITY(1,1) NOT NULL,
	ProcMessage VARCHAR(250) NOT NULL
	PRIMARY KEY (ProcID))

When I run the SP I receive this error:

(1 row(s) affected)
Msg 515, Level 16, State 2, Procedure InsertProducts, Line 20
Cannot insert the value NULL into column 'ProcMessage', table 'master.dbo.ProcedureLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

If I change (in the SP) to this, it works (I choose a static value instead of a variable):

INSERT INTO dbo.ProcedureLog (ProcMessage)
		VALUES ('ProductKey: ' + @ProductKey + ' with ProductID: ' + CONVERT(VARCHAR(25), 250) + ' inserted successfully!')

How do I insert my ProductID?
ProductID should be created at the beginning, when I insert the variables into "dbo.Products", of the code but do I need to retrive it somehow perhaps?
What is the most easiest and efficient way to proceed?

Thanks in advance!

Best Regards.
KF

matty
Posting Yak Master

161 Posts

Posted - 2008-08-05 : 06:05:43

Does Products table has a column ProductId which is set as identity column?
If so, after the INSERT INTO dbo.Products statement give
set @ProductID = SCOPE_IDENTITY()

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-08-05 : 09:18:16

quote:
Originally posted by matty

Does Products table has a column ProductId which is set as identity column?
If so, after the INSERT INTO dbo.Products statement give
set @ProductID = SCOPE_IDENTITY()

Thank you!

I have an IDENTITY in the Products-table.

After I modified the code to this it works great:

		-- Insert message into ProcedureLog
		set @ProductID = SCOPE_IDENTITY();		
		INSERT INTO dbo.ProcedureLog (ProcMessage)
		VALUES ('ProductKey: ' + @ProductKey + ' with ProductID: ' + CONVERT(VARCHAR(25), @ProductID) + ' inserted successfully!')

Thank you very much!

Best Regards,
KF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources