SP Help Please - 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)

SP Help Please

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-22 : 09:54:00

Hi All!
Below is some code from a stored proc I'm writing. In this proc I have 3 steps...

1. Add new customer data
2. Get the MAC (ID) of the new customer for use outside of this SP
3. Add any notes for this customer into a different table through another SP.

My problem right now is with the 3rd step, executing the stored proc for the note information. When this proc is ran the MAC is null. I understand why it is null. I'm just wondering if there is some way to get the MAC selected in step 2 into the @MAC for step three without having to run another query. I can't do SELECT @MAC = MAC on step 2 because I need the step 2 query for use outside of this sp. Does that make sense?

STEP 1

-- insert the new customer information
INSERT INTO TE_DCCustomer(Category_ID,CustomerName,PostBy)
VALUES (@Category_ID,UPPER(@CustomerName),@SBCUID);

STEP 2

-- get the MAC from the data you just entered
SELECT MAC
FROM TE_DCCustomer
WHERE CustomerName = @CustomerName AND PostBy = @UID;

STEP 3

IF @NoteInformation IS NOT NULL
BEGIN
-- add any note information for this customer
EXEC usp_TE_DC_addNote @MAC,'N/A',@NoteInformation,@SBCUID
END

THANKS!

Ryan Everhart
SBC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-22 : 10:20:19

how about:

Step 2a:
Select @Mac = mac From...

Step 2b:
Select Mac = @mac

Step 3:...

Corey

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-22 : 10:21:27

from this, I am guessing that MAC is an identity integer column? Maybe you could post you table structure.

If it is, you can use @@identity to give you that value. BOL example:


INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

you might do a select @mac=@@identity

HTH

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-22 : 10:23:15

It is and Identity and I will try that out.

all I need is this?
SELECT @@IDENTITY AS 'Identity'

Ryan Everhart
SBC

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-22 : 10:27:37

That did work Wanderer, thanks!

Ryan Everhart
SBC

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-22 : 11:01:45

No Prob :-)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 11:57:51

You should change @@IDENTITY to SCOPE_IDENTITY(). Just an fyi.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-06-22 : 12:00:57

Derrick,
This is what I have now...

SET @mac = @@IDENTITY

Should it be

SET @mac = @@SCOPE_IDENTITY()

OR

SET @mac = SCOPE_IDENTITY()

Thanks!
Ryan

Ryan Everhart
SBC

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 12:03:09

SET @mac = SCOPE_IDENTITY()

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources