SP Problem - 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 2000 Forums

Transact-SQL (2000)

SP Problem

Author

Topic

raydenl
Starting Member

16 Posts

Posted - 2003-09-22 : 03:01:38

Why does only the first INSERT statement work???

CREATE PROCEDURE insert_inventory
@item_name varchar(20),
@description varchar(100),
@notes varchar(255),
@amount varchar(8)
AS
DECLARE @item_id int
DECLARE @transaction_date datetime

IF (@item_name = '') SET @item_name = NULL
IF (@description = '') SET @description = NULL
IF (@notes = '') SET @notes = NULL
IF (@amount = '') SET @amount = NULL

SET @item_id = IDENT_CURRENT('inventory')
SET @transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-09-22 : 03:59:12

what is the error u get?

The Judgement of the Judge is as good as the Judge.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-22 : 04:08:59

Though BEGIN and END blocks are not required when the IF condition contains only one line, I've seen some pretty wierd bugs with it. Try:


IF (@Description = '')
BEGIN
   SET @Description = NULL
END

Why bother will this at all? Use the NULLIF function (IMHO its one of the most underutilized functions in SQL Server):


INSERT INTO inventory (item_name, item_description, notes) 
   VALUES (NULLIF(@item_name,''), NULLIF(@description,''), NULLIF(@notes,''))

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources