problem with output stored procedre

[ahmed17]

i have problem with stored procedure , i create procedure and it run good .

use MyTestDataBase
GO
create proc sp_with_outpt_parameters2
@id int ,
@name nvarchar(40)=Null,
@age int,
@phone nvarchar (40)=Null,
@CustomerId int output
AS
INSERT INTO Shippers VALUES (@id,@name,@age,@phone)
SELECT @CustomerId=@@Identity


but when i use this stored procedure it give me this message
-------------------------------------------------------------------
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@CustomerId".
-----------------------------------------------------------


DECLARE @MyIdent int
EXEC sp_with_outpt_parameters
@id=10,
@name = 'Ahmed',
@age = 35,
@phone = '15121052',
@CustomerId = @MyIdent OUTPUT
SELECT @MyIdent AS IdentityValue
SELECT id,[name], age,phone
FROM Shippers
WHERE @CustomerId = @MyIdent

[hspc]

I think this is what you really want:
SELECT id,[name], age,phone
FROM Shippers
WHERE CustomerId = @MyIdent
Note that the where statement compares CustomerId columns to @MyIdent variable.

[Thread1]

you should put your compound statements inside a BEGIN-END block


use MyTestDataBase
GO
create proc sp_with_outpt_parameters2
@id int ,
@name nvarchar(40)=Null,
@age int,
@phone nvarchar (40)=Null,
@CustomerId int output
AS
BEGIN
INSERT INTO Shippers VALUES (@id,@name,@age,@phone)
SELECT @CustomerId=@@Identity
END

[hspc]

you should put your compound statements inside a BEGIN-END block
No, Insert...Select is a single statement.
Also, the error that the OP gets is in the execution script not the SP creation script.

[jp140768]

How about using a SET statement instead?

Not sure if you can use:
SELECT @CustomerId=@@Identity

But you can definitly use Set @CustomerId = Ident_Current('Shippers') - well you can in SQL Server.

HTH

JP

[Thread1]

No, Insert...Select is a single statement.
Also, the error that the OP gets is in the execution script not the SP creation script.

I still find it as compound statement. Anyway, yes I overlooked that script I thought it was the creation of the SP hehe :D