Issue with OUTPUT Parameter

Question

question

Zack Jones asked Oct 26, '09

Issue with OUTPUT Parameter

In my stored procedure I'm using code like the following:

Create Procedure foo                    
@ID int OUTPUT,                    
@Subject varchar(255)                    
AS                    
SET NOCOUNT ON                     
Insert into Tablename (subject) values @subject) <br>                    
set @ID = Scope_Identity()                    
SET NOCOUNT ON

In my code I'm calling the stored procedure like so:

Dim param as new sqlparameter("@ID", sqldbtype.int, 4, ParameterDirection.Output)<br>                    
cmd.parameters.add(param)<br>                    
cmd.parameters.add("@Subject", sqldbtype.varchar, 200).Value = "Test"

When I try to call the stored procedure I get an error that the stored procedure is looking for an @ID parameter which was not provided. Can anyone give me an idea why?

sql-server-2005 stored-procedures parameters

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-27T03:36:22Z

Madhivanan answered Oct 27, '09

You need to give some dummy value to @ID

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-27T06:30:48Z

Kristen answered Oct 27, '09

If your application needs the value of the assigned ID then you need to do as Madhivanan says. If you don't need the ID [at that point in your application] change the Sproc to:

Create Procedure foo            
    @ID int = NULL OUTPUT,            
    @Subject varchar(255)            
AS            
...

this will provide a Default value of NULL to @ID if it is not explicitly included in the caller's parameter list

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-27T09:13:58Z

Zack Jones answered Oct 27, '09

Thanks for the suggestions. I tried them both but still can't get a return value from calling the stored procedure. I was able to get it working by using the following though (which I know isn't the "right" way to do it but at least it works for me:

SET NOCOUNT ON
Insert into Tablename (subject) values @subject)
Select Scope_Identity()
SET NOCOUNT ON

Calling it with intTemp = cmd.ExecuteScalar returns the scope_identity value everytime. I really wanted to use a return parameter but I can't spent any more time fooling with this. I'll try again sometime in the future.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question