January 19, 2004 at 8:44 am
I wrote a Stored Procedure that checks to see if a value is there, if not then it adds it to the table. OK, I'd like to think that I wrote one that does that but it doesn't work.
Here it is:
CREATE PROCEDURE dbo.procAddContactAddress
(
@Contact int,
@Address int,
@ContactAddressPK int = NULL OUTPUT,
@RetCode int=NULL OUTPUT
)
AS
SELECT tblContactAddressPK, tblContactFK, tblAddressFK
FROM dbo.tblContactAddress
WHERE (tblContactFK = @Contact) AND (tblAddressFK = @Address)
IF @@ROWCOUNT = 1
SELECT @ContactAddressPK= @@IDENTITY,
@RetCode=1
ELSE
INSERT INTO dbo.tblContactAddress
(tblContactFK, tblAddressFK)
VALUES (@Contact, @Address)
IF @@ROWCOUNT = 1
SELECT @ContactAddressPK= @@IDENTITY,
@RetCode=2
GO
I'm calling it from VB using an ADODB.Command and it's returning a NULL Value for @ContactAddressPK.
What am I missing?
January 19, 2004 at 9:42 am
HI ZOUL,
THE @@IDENTITY WOULD PROBABLY NOT WORK WITH SELECT. ITS WORKS WITH INSERT, SELECT INTO AND BULK COPY STATEMENTS.
TRY USING "IF EXISTS" TO CHECK WHETHER THE RECORD IS THERE OR NOT.
CHEERS:-)
January 19, 2004 at 11:01 am
Thanks for the tip. I'm getting close.
I changed it to this and it was still returning Null even thought the record is in there. What I did was remove the ELSE statment and everything after and it worked. What am I missing that it still executes the second part of the statment?
Thanks.
CREATE PROCEDURE dbo.procAddContactAddress
(
@Contact int,
@Address int,
@ContactAddressPK int = NULL OUTPUT,
@RetCode int=NULL OUTPUT
)
AS
IF EXISTS
(
SELECT tblContactAddressPK
FROM dbo.tblContactAddress
WHERE (tblContactFK = @Contact) AND (tblAddressFK = @Address)
)
SELECT @ContactAddressPK= (
SELECT tblContactAddressPK
FROM dbo.tblContactAddress
WHERE (tblContactFK = @Contact) AND (tblAddressFK = @Address)),
@RetCode=1
ELSE
INSERT INTO dbo.tblContactAddress
(tblContactFK, tblAddressFK)
VALUES (@Contact, @Address)
SELECT @ContactAddressPK= @@IDENTITY,
@RetCode=2
GO
January 19, 2004 at 2:43 pm
How about something like this...
CREATE PROCEDURE dbo.procAddContactAddress
(
@Contact int,
@Address int,
@ContactAddressPK int = NULL OUTPUT,
@RetCode int=NULL OUTPUT
)
AS
SELECT @ContableAddressPK = tblContactAddressPK
FROM dbo.tblContactAddress
WHERE tblContactFK = @Contact AND tblAddressFK = @Address
IF @ContactAddressPK IS NULL
BEGIN
INSERT INTO dbo.tblContactAddress(tblContactFK, tblAddressFK)
VALUES(@Contact, @Address)
SELECT @ContactAddressPK = SCOPE_IDENTITY(), @RetCode = 2
RETURN @RetCode
END
RETURN 1
GO
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 19, 2004 at 3:09 pm
That was the ticket!
Thanks,
Dennis
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply