SQL Msg 8162, Level 16, State 2

When I execute the SQL procedure I get the following error message...

Msg 8162, Level 16, State 2, Procedure spITS_FullCaseLabels, Line 0
Formal parameter '@iUserID' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Please see the attached jpg file for a screen shot and more information.
Thanks in advance.
msg-8162.JPG
tesla764Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
Doesn't appear that the sp has the iUserID parameter defined as an output parameter in the proc definition.  You'll need to alter the procedure to mark that as an output parameter.
tesla764Author Commented:
Could you tell me how to do that?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please remove the OUTPUT for all the parameters excel the result and result_code parameters.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PadawanDBAOperational DBACommented:
Sure thing!  Easiest way:  right click the stored procedure > script stored procedure as > alter to > new query window.  Then all you'll need to do is look for the "@iUserID bigint" and change that to "@iUserID bigint OUTPUT".


Edit:
Guy does bring up a good point.  Unless your stored procedure is doing something that assigns a value to the @iUserID variable, it's not really going to do anything.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tesla764Author Commented:
Do you mean in the EXEC area remove all the OUTPUT statements?
What do you mean by...
excel the result and result_code parameters
Could you please explain.
I have never encountered this situation before.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have the code like this :
declare @variable_1 data_type
 , @variable_2 data_type
 ...etc...
exec procedure_name @variable_1 OUTPUT
   ,  @variable_2 OUTPUT
    ... etc  ...

select @variable_1
    , @variable_2 
  ... etc ...

Open in new window


now, the BIG question is: should the stored procedure return data into all those (output) parameters or not.

if yes: the parameter code is wrong, as only the first 2 parameters (in the procedure itself, not in the calling code) are defined to be OUTPUT. to change that, you have to EDIT the stored procedure, and add OUTPUT to the parameter declaration. when you have the code of the stored procedure opened, you shall see that easily

if not: remove the OUTPUT keyword for all the parameters in the EXEC procedure_name part which are NOT returning any value, but for which you have to provide some input data so the procedure knows on what to work on.
for those, you need to SET the data, which needs to happen between the DECLARE and the EXEC part, like this:
set @variable = some_value

if all of this is "chinese" to you, I think you are completely wrong person on the task you are trying to achieve, in the sense that unless you do some SQL (T-SQL) training, you should keep your hands from the keyboard
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.