Help with SQL Server 2000 Stored Procedure

[ChrisQuick]

I have a stored procedure I am working on for an online support database. I want to use a Stored Procedure in SQL Server 2000 to add the Profile and Contact information of a customer at the same time.

I have two tables: Contacts, UserProfile.
The primary key in Contacts (contact_id) is an Identity as serves to as the foreign key in User Profile.

When I run the SP in SQL Query Analyzer, I get the error:
Procedure 'AddNewContact' expects parameter '@ContactID', which was not supplied.
Stored Procedure: gtgsupport.dbo.AddNewContact

The parameter @ContactID is defined as an OUTPUT parameter, so I don't know what is wrong with it!


Here is the complete SP Definition:

CREATE PROCEDURE dbo.AddNewContact(
@FirstName_2 [varchar](50),
@LastName_3 [varchar](50),
@Honorific_4 [varchar](4),
@AreaCode_5 [varchar](3),
@PhonePrefix_6 [varchar](3),
@PhoneNumber_7 [varchar](4),
@PhoneExt_8 [varchar](5) = null,
@theFaxAreaCode [varchar](3) = null,
@FaxPreFix_9 [varchar](3) = null,
@FaxNumber_10 [varchar](4) = null,
@CellAreaCode_11 [varchar](3) = null,
@CellPrefix_12 [varchar](3) = null,
@CellNumber_13 [varchar](4) = null,
@EmailAddressName_14 [varchar](20),
@EmailAddressServer_15 [varchar](20),
@EmailAddressDomain_16 [varchar](3),
@strProfileName [varchar](12),
@strPassword [varchar](8),
@ContactID int OUTPUT)

AS

INSERT INTO [gtgsupport].[dbo].[Contacts] (
[FirstName],[LastName],[Honorific],
[AreaCode],[PhonePrefix],[PhoneNumber],[PhoneExt],
[FaxAreaCode],[FaxPreFix],[FaxNumber],
[CellAreaCode], [CellPrefix],[CellNumber],
[EmailAddressName],[EmailAddressServer],[EmailAddressDomain]
)


VALUES (
@FirstName_2,@LastName_3,@Honorific_4,
@AreaCode_5,@PhonePrefix_6, @PhoneNumber_7,@PhoneExt_8,
@theFaxAreaCode,@FaxPreFix_9,@FaxNumber_10,
@CellAreaCode_11,@CellPrefix_12,@CellNumber_13,
@EmailAddressName_14,@EmailAddressServer_15, @EmailAddressDomain_16
)

Set @ContactID = @@IDENTITY
INSERT INTO [gtgsupport].[dbo].[UserProfile] (
[Contact_id],[ProfileUserName],[ProfilePassword]
)

VALUES (
@ContactID,@strProfileName,@strPassword
)
GO cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[Cagliostro]

if the @ContactID is OUTPUT you should give some value to him. somewhere in function.

for example put at the end of function

set @ContactID = 0 John Fill

ivfmd@mail.md

 

[ChrisQuick]

I am setting the value to equal the @@Identity like this:

Set @ContactID = @@IDENTITY

right after the first insert statement.

cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[Cagliostro]

in this case define as both INPUT and OUTPUT. John Fill

ivfmd@mail.md

 

[ChrisQuick]

How do you do that? cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[Cagliostro]

try that:
CREATE PROCEDURE dbo.AddNewContact(
.....
@ContactID int = 0 OUTPUT) John Fill

ivfmd@mail.md

 

[ChrisQuick]

Ok, I've changed the procedure and it is doing the first insert without error. But for some reason the second insert statement is not firing. Here is what I have now:

CREATE PROCEDURE dbo.AddNewContact(
@FirstName_2 [varchar](50),
@LastName_3 [varchar](50),
@Honorific_4 [varchar](4),
@AreaCode_5 [varchar](3),
@PhonePrefix_6 [varchar](3),
@PhoneNumber_7 [varchar](4),
@PhoneExt_8 [varchar](5) = null,
@theFaxAreaCode [varchar](3) = null,
@FaxPreFix_9 [varchar](3) = null,
@FaxNumber_10 [varchar](4) = null,
@CellAreaCode_11 [varchar](3) = null,
@CellPrefix_12 [varchar](3) = null,
@CellNumber_13 [varchar](4) = null,
@EmailAddressName_14 [varchar](20),
@EmailAddressServer_15 [varchar](20),
@EmailAddressDomain_16 [varchar](3),
@strProfileName [varchar](12),
@strPassword [varchar](8),
@ContactID int = 0 OUTPUT)

AS

INSERT INTO [gtgsupport].[dbo].[Contacts] (
[FirstName],[LastName],[Honorific],
[AreaCode],[PhonePrefix],[PhoneNumber],[PhoneExt],
[FaxAreaCode],[FaxPreFix],[FaxNumber],
[CellAreaCode], [CellPrefix],[CellNumber],
[EmailAddressName],[EmailAddressServer],[EmailAddressDomain]
)


VALUES (
@FirstName_2,@LastName_3,@Honorific_4,
@AreaCode_5,@PhonePrefix_6, @PhoneNumber_7,@PhoneExt_8,
@theFaxAreaCode,@FaxPreFix_9,@FaxNumber_10,
@CellAreaCode_11,@CellPrefix_12,@CellNumber_13,
@EmailAddressName_14,@EmailAddressServer_15, @EmailAddressDomain_16
)

Select @ContactID = @@IDENTITY
INSERT INTO [gtgsupport].[dbo].[UserProfile] (
[Contact_id],[ProfileUserName],[ProfilePassword]
)

VALUES (
@ContactID,@strProfileName,@strPassword
)
GO
cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[Cagliostro]

there is something with @@identity. I don't know exactly what is it. John Fill

ivfmd@mail.md