question

Zack Jones avatar image
Zack Jones asked

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-2005stored-proceduresparameters
10 |1200

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

Madhivanan avatar image
Madhivanan answered

You need to give some dummy value to @ID

10 |1200

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

Kristen avatar image
Kristen answered

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

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

Zack Jones avatar image
Zack Jones answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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