Two inserts in one procedure - 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)

Two inserts in one procedure

Author

Topic

jrp210
Starting Member

7 Posts

Posted - 2003-10-10 : 11:26:45

My stored proc is inserting member data when a user submits an asp page. Pretty straightforward stuff. Can I include another insert statement into a different table in the same stored procedure based on the type of member?

For example,
A user is added to the member table AND added to another table only if their user type is 6?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-10 : 11:33:02

You can compile as much DML as you like into a stored proc. Curious, did you try?

Jay White
{0}

jrp210
Starting Member

7 Posts

Posted - 2003-10-10 : 12:00:22

Yep, but I don't think I have the structure right. I am doing my insert:

SET NOCOUNT ON
Insert Into Members (FirstName, LastName, MemberType)
Values (@First, @Last, @MType)

Select @@Identity As NewMember

*** what I am trying to accomplish ***

If @Mtype = 6
Begin
Insert Into Member_Supervisor (MemId, SupId)
Values (NewMember, @Sup)
End

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-10 : 12:13:05

SET NOCOUNT ON
declare @newmember int
Insert Into Members (FirstName, LastName, MemberType)
Values (@First, @Last, @MType)

Select @newmember = @@Identity

*** what I am trying to accomplish ***

If @Mtype = 6
Begin
Insert Into Member_Supervisor (MemId, SupId)
Values (@NewMember, @Sup)
End

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-10 : 12:27:23

[code]

USE Northwind
GO

CREATE PROC myProc
@CustomerID char(10), @EmployeeId Int, @OrderDate datetime
AS

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)
SELECT @CustomerID, @EmployeeId, @OrderDate

IF @@Error <> 0 Return -1

IF @EmployeeID = 6
BEGIN
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT SCOPE_IDENTITY(), 1, 1, 1, 1

IF @@Error <> 0 Return -1
END
GO

DECLARE @x datetime, @rc int
SELECT @x = Getdate()

EXEC @rc = MyProc 'ALFKI', 6, @x

IF @rc <> 0 PRINT 'PROC FAILED'
ELSE BEGIN
PRINT 'PROC SUCCESS'
SELECT * FROM Orders WHERE OrderDate = @x
SELECT * FROM [Order Details] d
WHERE OrderID IN (SELECT OrderId FROM Orders o WHERE o.OrderDate = @x)
END

-- Clean up

DELETE FROM [Order Details]
WHERE OrderID IN (SELECT OrderId FROM Orders o WHERE o.OrderDate = @x)
DELETE FROM Orders
WHERE OrderDate = @x

EXEC @rc = MyProc 'BRETT', 6, @x

IF @rc <> 0 PRINT 'PROC FAILED'
ELSE BEGIN
PRINT 'PROC SUCCESS'
SELECT * FROM Orders WHERE OrderDate = @x
SELECT * FROM [Order Details] d
WHERE OrderID IN (SELECT OrderId FROM Orders o WHERE o.OrderDate = @x)
END

DROP PROC myProc
GO

[/code]

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-10 : 12:40:13

jrp210, use SCOPE_IDENTITY() instead of @@IDENTITY. @@IDENTITY can be wrong. Take a look at the differences in SQL Server Books Online.

Your statement would look like this:

Select @newmember = SCOPE_IDENTITY()

Tara

jrp210
Starting Member

7 Posts

Posted - 2003-10-10 : 12:46:30

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources