stored procedur to update multiple tables

[Enobong Adahada]

Hi, i have this stored prcedure to update multiple tables in sqlserver DB, when i run it , i get an saying tbpassport,tbaccounts, tbpermanentaddress and tbcontactaddress cannot allow nulls. this is the code, where am i getting it wrong. CREATE PROCEDURE InsertEmployeeRecord @emp_id char(5), @surname char(25), @fisrtname char(25), @middlename char(25), @sex char(10), @date_of_birth char(12), @ministry_code char(3), @dept_code char(3), @nationality char(15), @state_code char(3), @lga_code char(3), @designation char(15), @date_of_employment char(12), @unit_code char(3), @employement_type char(15), @marital_status char(10), @religion char(10), @glevel char(10), @step char(8), @picture image,@filename char(52), @bank_name char(52), @bank_code char(3), @sortcode char(9), @branch char(100), @account_no char(40), @pcity char(45), @ptown char(45), @pstreet char(100), @pphone char(200), @ccity char(45), @ctown char(45), @cstreet char(100), @cphone char(200) AS BEGIN SET NOCOUNT ON INSERT tbemployee (emp_id,surname,firstname,middlename, sex,date_of_birth,ministry_code,dept_code,nationality, state_code,lga_code,designation,date_of_employment, unit_code,employment_type,marital_status,religion,glevel,step) VALUES(@emp_id, @surname, @fisrtname, @middlename, @sex, @date_of_birth, @ministry_code, @dept_code, @nationality, @state_code, @lga_code, @designation, @date_of_employment, @unit_code, @employement_type, @marital_status, @religion, @glevel, @step) INSERT INTO tbpassport (emp_id,filename,picture) VALUES(@emp_id, @filename, @picture) INSERT INTO tbaccounts (emp_id,bank_code,bank_name,sortcode,account_no,branch) VALUES (@emp_id, @bank_code, @bank_name, @sortcode, @account_no, @branch) INSERT INTO tbpermanentaddress (emp_id, city, town, street, phone) VALUES (@emp_id, @pcity, @ptown, @pstreet, @pphone) INSERT INTO tbcontactaddress (emp_id,city,town,street, phone) VALUES (@emp_id,@ccity, @ctown, @cstreet, @cphone) END GO thanks.

[Wayne Gaylard]

It seems as though your tables have been set up not to accept null values. You either need to edit the table to accept nulls (probably not the best approach), or check whether your input values are not null before running the stored procedure(my preferrred approach) .

[The Man from U N C L E]

you kind of lost the thread there with your second post, however.. Either change the tables to allow nulls, Or ensure no null values are passed to the procedure, Or best of all, use isnull checks for each insert. e.g.

INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES
(
isnull(@emp_id, '')
,isnull(@ccity, '')
,isnull(@ctown, '')
,isnull(@cstreet, '')
,isnull(@cphone, '')
)

Another consideration is that rather than passing so many parameters you may be better off passing it all wrapped up in a single XML parameter.

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles]  [My Website]