A really stupid question! - 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

SQL Server Development (2000)

A really stupid question!

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-09 : 09:32:04

What's wrong with this. I want to take the value of my select and insert into the new record.

CREATE PROCEDURE SP_SPE_INSERTOP
(@STARTDATE datetime,@COMPLETEDATE datetime,@WC char(10),@MO int,@QTY float,@LN int,@uName char)
AS
DECLARE @ROW AS INT
DECLARE @LN_NO INT

SELECT @LN_NO=ROW_ID FROM SPE_PLAN_DTL WHERE LN_NO=@LN AND MOHEADERKEY=@MO

INSERT INTO SPE_PLAN_DTL (STARTDATE, COMPLETEDATE,COMPONENT,MODIFYDATE,MOHEADERKEY,QTY,LN_NO,MODIFYBY,ROW_ID)VALUES(@STARTDATE,@COMPLETEDATE,UPPER(@WC),GETDATE(),@MO,@QTY,@LN,@uName,@LN_NO)

GO

Thanks for any help!

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-10-09 : 09:52:35

What is the error message you get? It looks correct syntactly.

Justin

"I want to thank you. You could've given us help, but you've given us so much more."

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-09 : 10:15:45

Sorry - I didn't specify. I don't get an error - the syntax is correct. I just get a null value. It's like the @LN_NO doesn't pick up the value from the select statement. Any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-09 : 10:29:38

@LN_NO is probably NULL for some reason.

SELECT @LN_NO=ROW_ID
FROM SPE_PLAN_DTL
WHERE LN_NO=@LN AND MOHEADERKEY=@MO

IF @LN_NO IS NULL BEGIN
-- code handling of null condition here
END ELSE BEGIN

INSERT INTO SPE_PLAN_DTL
(STARTDATE,COMPLETEDATE,COMPONENT,MODIFYDATE,MOHEADERKEY,QTY,LN_NO,MODIFYBY,ROW_ID)
VALUES(@STARTDATE,@COMPLETEDATE,UPPER(@WC),GETDATE(),@MO,@QTY,@LN,@uName,@LN_NO)
END
GO

mr_mist
Grunnio

1870 Posts

Posted - 2003-10-09 : 10:31:32

Not meaning to state the obvious, but have you checked that there is actually a row that matches on LN_NO=@LN AND MOHEADERKEY=@MO ?

-------
Moo. :)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-09 : 10:43:45

Well . . . on further examination, I did find the record had a null value. The only one in the gosh darn db, and I had to pick it.

OK, this is me feeling like a boob. Thanks for your help!

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-09 : 10:59:42

Don't feel alone. There are lots of boobs here.

What're the chances of your test finding the one record with a NULL? My tests find those situations all the time to make sure I spend more time debugging than writing new code.

Also, say hello to my leetle friend: IsNull

INSERT INTO SPE_PLAN_DTL (STARTDATE,COMPLETEDATE,COMPONENT,MODIFYDATE,MOHEADERKEY,QTY,LN_NO,MODIFYBY,ROW_ID)
VALUES(@STARTDATE,@COMPLETEDATE,UPPER(@WC),GETDATE(),@MO,@QTY,@LN,@uName,IsNull(@LN_NO, 0))

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-09 : 11:03:56

Hello leetle friend!

Thanks again! I just LOVE this site!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources