SP wants input for optional and output parameters. 2

[grnzbra]

I have the following:

CREATE Procedure InsertTestProc
(
@Name VARCHAR(50),
@Address VARCHAR(50) = NULL,
@City VARCHAR(50),
@State VARCHAR(15),
@Zip VARCHAR(10),
@NewKey INT OUTPUT
)

When I call it from the Query Analyzer using:

InsertTestProc 'Doe',,'Anycity','Anystate','12345'

I get an error message saying that on Line1 (I assume that means line 1 of the QA) there is an error at ','.

If I supply a value for the @Address parameter, I get an error message indicating that there should be a value for @NewKey.

I've checked Books Online, and this seems like it should work. What am I doing wrong?

 

[mrdenny]

When using an OUTPUT variable, you have to define a variable, and tell SQL to store the outputted data in that variable.

Declare @NewKey int
exec InsertTestProc @Name='Doc', 
   @Address=null,
   @City='AnyCity',
   @State='Anystate',
   @Zip='12345',
   @NewKey=@NewKey OUTPUT
select @NewKey

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ashstampede]

ah in query anazlyer is does that, if you select the sp and click open, for the variable that is of output type just select as no. however when you call that store procedure from outside of query analyzer like say from a asp.net app. it won't throw that error no parameter supplied.

 

[grnzbra]

Thank you both.