Stored Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

kdford10
Starting Member

7 Posts

Posted - 2010-08-18 : 16:42:03

Below, I am trying to create a simple stored procedure that insert values into a table. But when I execute the code, error message ''
'Cannot insert the value NULL into column 'LoanID', table 'BankLoans.dbo.Loan'; column does not allow nulls. INSERT fails. The statement has been terminated.'
What does this means?

CREATE PROCEDURE dbo.usp_insert_Loan
(
@LoanID int
,@BranchName varchar(30)
,@LoanOfficerLastName varchar(30)
,@LoanOfficerFirstName varchar(30)
,@BorrowerLastName varchar(30)
,@BorrowerFirstName varchar(30)
,@LoanDate datetime
,@LoanType varchar(30)
,@Address1 varchar(30)
,@City varchar(30)
,@State char(2)
,@Zip int
)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

CREATE TABLE dbo.BankInfo
(
LoanID int IDENTITY

);

GO

INSERT INTO dbo.Loan(BranchID)
VALUES ('5');
INSERT INTO dbo.Loan(BorrowerID)
VALUES ('164');
INSERT INTO dbo.Loan(PropertyID)
VALUES ('4');
INSERT INTO dbo.Loan(ApplicationNumber)
VALUES ('150349');
INSERT INTO dbo.Loan(LoanDate)
VALUES ('2011-01-04');
INSERT INTO dbo.Loan(LoanAmount)
VALUES ('170000');
INSERT INTO dbo.Loan(LoanType)
VALUES ('Open');

GO

SELECT BranchID, BorrowerID, PropertyID, ApplicationNumber, LoanDate, LoanAmount, LoanType
FROM dbo.Loan
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-18 : 16:49:09

It should be one insert statement:

INSERT INTO Table1 (Column1, Column2, Column3, ...)
VALUES (1, 2, 3, ...)

You'll need to remove GO if you want the insert inside the stored procedure.

Why would you create a table from within the stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-18 : 17:06:37

INSERT INTO puts an entire set of ROWS into a table.

A NULL loan_id makes no sense. All identifiers have to be known by definition.

zip_code (not just zip, if you want to follow ISO-11179 rules) is CHAR(5) and never a numeric. It will not work that way.

Why is almost everything VARCHAR(30). The standard for US postal addresses is based on a 10-pitch typewriter making labels for a 3.5 inch five line address label.

Why are you creating a table inside a procedure?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:29:06

I cant understand the purpose of table created inside SP as you're not using it at all.Also the error message suggests like its a PK column. why not make it identity type then so that you dont have to explicitly pass a value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources