output param not working? - 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

Transact-SQL (2000)

output param not working?

Author

Topic

RobJ
Starting Member

4 Posts

Posted - 2005-08-21 : 12:15:12

Hi guys,

I've got a stored procedure that users an output param to return the last identity made for the table (@@Identity). I get this error when I try to execute it:

Server: Msg 201, Level 16, State 4, Procedure sp_add_user_details, Line 0
Procedure 'sp_add_user_details' expects parameter '@Customer_Number', which was not supplied.

here is the call to it:

Exec sp_add_user_details mr, Rob, Johnno, email, add1, add2, sup, 4021, oz, 325, 325, 0

and here is the stored procedure itself:

CREATE Procedure sp_add_user_details
(
    @Title char(3),
    @First_Name varchar(25),
    @Last_Name varchar(25),
    @Email varchar(50) = NULL,
    @Address1 varchar(50),
    @Address2 varchar(50) = NULL,
    @Suburb varchar(25),
    @Post_Code int,
    @Country varchar(25),
    @Phone numeric,
    @Fax numeric = NULL,
    @Mailing_List bit = 0,
    @Customer_Number int OUTPUT
)
AS

INSERT INTO JP_Customer_Details
(
    Title,
    First_Name,
    Last_Name,
    Email,
    Address1,
    Address2,
    Suburb,
    Post_Code,
    Country,
    Phone,
    Fax,
    Mailing_List
)

VALUES
(
    @Title,
    @First_Name,
    @Last_Name,
    @Email,
    @Address1,
    @Address2,
    @Suburb,
    @Post_Code,
    @Country,
    @Phone,
    @Fax,
    @Mailing_List
)

SELECT
    @Customer_Number = @@Identity
GO

Help will be repaid with love and cookies :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-21 : 12:28:08

Hi
You need to declare a variable to store the value of the output parameter and then add it to your parameters with the OUTPUT keyword:

DECLARE @Customer_Number INT
Exec sp_add_user_details mr, Rob, Johnno, email, add1, add2, sup, 4021, oz, 325, 325, 0, @Customer_Number OUTPUT

Mark

RobJ
Starting Member

4 Posts

Posted - 2005-08-21 : 12:29:15

Cheers :) All my cookies are belong to you

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-21 : 15:18:16

No worries!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources