A more optimised way of doing the following?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

A more optimised way of doing the following??

Author

Topic

stats
Starting Member

10 Posts

Posted - 2006-02-04 : 15:17:57

Initially I want to insert two variables in to a the jobRequest table (which I'm using to maintain the one to many cardinality between the userTable and the grassCutting table), the jobRequest table assigns an autonumber value to jobRequest.jobID. I want to take this value and insert it into the grasscutting table with the assiciated variable. I had an attempt, which works. But its seems to be terribly bad code of which I'm sure can be optimesed. Can someone please suggest what I can do.

CREATE PROCEDURE addGrassRequest
@userID int, @street nvarchar(30), @details nvarchar(50)
AS
DECLARE
@nowDate datetime, @newjobID int

SELECT @nowDate = GETDATE()

INSERT INTO jobRequest (userID, dateReported)
VALUES(@userID, @nowDate)

SELECT @newjobID = req.jobID FROM jobRequest req WHERE req.userID = @userID AND req.dateReported = @nowdate

INSERT INTO grassCutting (jobID, street, details)
VALUES(@newjobID, @street, @details)

RETURN 1

GO

STaTs

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-02-04 : 16:33:00

You can use SCOPE_IDENTITY() to get the last identity value used in an insert. This way you won't have to do the extra select statement.

CREATE PROCEDURE addGrassRequest
@userID int, @street nvarchar(30), @details nvarchar(50)
AS

INSERT INTO jobRequest (userID, dateReported)
SELECT @userID, GETDATE()

INSERT INTO grassCutting(jobID, street, details)
SELECT SCOPE_IDENTITY(), @street, @details

RETURN 1

GO

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-04 : 16:33:05

[code]CREATE PROCEDURE addGrassRequest
@userID int, @street nvarchar(30), @details nvarchar(50)
AS
DECLARE @newjobID int
declare @error int

INSERT INTO jobRequest (userID, dateReported)
VALUES(@userID, getDate())

SELECT @newjobID = scope_identity(), @error = @@error

if @error <> 0 return (-1) --or any other 'failure' code

INSERT INTO grassCutting (jobID, street, details)
VALUES(@newjobID, @street, @details)

RETURN 1

GO[/code]Five seconds late!

stats
Starting Member

10 Posts

Posted - 2006-02-05 : 10:30:25

cheers for the help. I'm learning alot about SQL SERVER all tha time.

STaTs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources