problem with output stored procedre

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

Code:

---

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".
-----------------------------------------------------------

Code:

---

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

---

I think this is what you really want:

Quote:

---

SELECT id,[name], age,phone
FROM Shippers
WHERE CustomerId = @MyIdent

---

Note that the where statement compares CustomerId columns to @MyIdent variable.

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

Code:

---

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

---

Quote:

---

Originally Posted by Thread1

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.

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

Quote:

---

Originally Posted by hspc

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