sql pass through

* Find out the member_no of the new member
If SQLExec(ThisForm.nHandle, "Select @@identity") < 0


nNewMemberID = sqlresult.exp

ตัวอย่างการสร้าง Store procedure
CREATE PROCEDURE updateadult
@member_no member_no,
@lastname shortstring = NULL,
@firstname shortstring = NULL,
@middleinitial letter = NULL,
@street shortstring = NULL,
@city shortstring = NULL,
@state statecode = NULL,
@zip zipcode = NULL,
@phone_no phonenumber = NULL
AS
DECLARE @sqlstring1 varchar(255)
DECLARE @sqlstring2 varchar(255)

IF @lastname = NULL AND @firstname = NULL AND
@middleinitial = NULL AND @street = NULL AND
@city = NULL AND @state = NULL AND
@zip = NULL AND @phone_no = NULL
BEGIN
PRINT "Nothing to do."
RETURN
END

SELECT @sqlstring1 = NULL
SELECT @sqlstring2 = NULL

IF @lastname != NULL
SELECT @sqlstring1 = @sqlstring1 + "lastname = '" +
@lastname + "',"
IF @firstname != NULL
SELECT @sqlstring1 = @sqlstring1 + "firstname = '" +
@firstname + "',"


BEGIN TRANSACTION
IF @sqlstring1 != NULL
BEGIN
SELECT @sqlstring1 = "UPDATE member SET " +

SUBSTRING(@sqlstring1, 1,
DATALENGTH(@sqlstring1) - 1) +
" WHERE member_no = " +
CONVERT(char(6), @member_no)
EXECUTE (@sqlstring1)
IF @@error != 0
BEGIN
ROLLBACK TRAN
RAISERROR('The member information was not saved.
Feel free to try again.',16,1)
RETURN
END
END
F @sqlstring2 != NULL
BEGIN
SELECT @sqlstring2 = "UPDATE adult SET " +
SUBSTRING(@sqlstring2, 1,
DATALENGTH(@sqlstring2) - 1) +
" WHERE member_no = " +
CONVERT(char(6), @member_no)
EXECUTE (@sqlstring2)
IF @@error != 0
BEGIN
ROLLBACK TRAN
RAISERROR('The member information was not saved.
Feel free to try again.',16,1)
RETURN
END
END

COMMIT TRANSACTION

โปแกรม Store procedure ในการ Remove member
CREATE PROCEDURE removemember
@member_no member_no
AS
IF NOT EXISTS
(SELECT * FROM member WHERE member_no = @member_no)
BEGIN
PRINT " Member number not found in Member table."
RETURN
END

IF EXISTS (SELECT member_no FROM juvenile
WHERE @member_no = adult_member_no)
BEGIN
RAISERROR('This member can not be deleted. He/she is
an adult with active juveniles.',16,1)
RETURN
END

IF EXISTS (SELECT member_no FROM loan
WHERE member_no = @member_no)
BEGIN
RAISERROR("This member can not be deleted. He/she has
active loans.",16,1)
RETURN
END

BEGIN TRANSACTION

IF EXISTS (SELECT member_no FROM loanhist
WHERE member_no = @member_no)
BEGIN
PRINT 'Deleting Loan History information'
DELETE loanhist WHERE member_no = @member_no
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END
END

IF EXISTS (SELECT member_no FROM reservation
WHERE member_no = @member_no)
BEGIN
PRINT 'Deleting Loan Reservation information'
DELETE reservation WHERE member_no = @member_no
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END
END

IF EXISTS (SELECT member_no FROM juvenile
WHERE member_no = @member_no)
BEGIN
DELETE juvenile WHERE member_no = @member_no
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END
END
ELSE IF EXISTS (SELECT member_no FROM adult
WHERE member_no = @member_no)
BEGIN
DELETE adult WHERE member_no = @member_no
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END
END

DELETE member WHERE member_no = @member_no
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END

COMMIT TRANSACTION

edit @ 2006/10/14 12:11:13